mysql/php

[DeathByTray]

I got stuck and can't figure out why.

$gpu = "a5970,a5870,a5850,a5830,a5770,a5750,a5670,a480,a470,";
$gpuprize = "'699','389','289','229','134','119','79','499','349',";
$stuffit = "INSERT INTO	price (".$gpu." date) VALUES ('".$gpuprize."'CURDATE())";
mysql_query($stuffit) or die(mysql_error());

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '699','389','289','229','134','119','79','499','349','CURDATE())' at line 1

Any idea?
I first thought it was due to missing backticks in $gpuprize, added them and still not working.

 

[Deleted member 3]

You're missing commas between the column names and the values. Also, you can't just insert an endless amount of values. One value per colum, ie run a query for each value.

 

[FordGT90Concept]

You also shouldn't have to put apostrophes around numbers:

$gpuprize = "699, 389, 289, 229, 134, 119, 79, 499, 349,";
$stuffit = "INSERT INTO price (" . $gpu . " date) VALUES (" . $gpuprize . " CURDATE())";

That's assuming a5970, a5870, etc. are column names in table "price."

 

[DeathByTray]

Ahh.. thanks a lot FordGT90Concept, works like a charm.
Those damn apostrophes in VALUES were not needed. Weird, I thought they were necessary.

DanTheBanjoman
Commas as well as enough columns are there, I was just under the impression that you need apostrophes as well as quotation marks when you're using variables in VALUES.

 

[FordGT90Concept]

I found that MySQL often prefers ` (to the left of 1 on QWERTY keyboards) to '. If you replaced all the apostrophes with tlides (`), it probably would have worked. You only need to put ` around strings.

 

[DeathByTray]

Interesting, will keep an eye on that.

While we're at it, I've got another question.
As you can see, I'm using a prefix - 'a' in this case - for all the GPU names. That's because mysql doesn't seem to like numbers only. Do you know of a better workaround or a fix?

 

[FordGT90Concept]

No offense but you are doing it wrong. XD

There should be a table containing a list of graphics cards. Price should either be part of that table or in a separate table. For example...

GfxCards Table:

id|model|vram
1|"5970"|2048
2|"5870"|1024

GfxCardPrice Table:

id|gfx_id|price|date
1|1|699|NOW()
2|2|389|NOW()

On that second table, if you want to log a new price on 5870, for example, it would appear as:

id|gfx_id|price|date
1|1|699|NOW()
2|2|389|NOW()
3|2|350|NOW()

If you were looking for the price of a 5870, you would query all WHERE gfx_id = 2 and take the most recent entry.

 

[W1zzard]

in my opinion it is good practice to put field values into ' even if they are numbers

also dont forget to clean user input (includes $_POST and $_GET), look at mysql_escape_string

i use the backtick ` only when escaping field names, for example when you call your field "date" .. mysql thinks you want to call some date function, use `date` to identify it as a field name, better is to call it ts like timestamp or dateadded or something that is not a mysql keyword, will save you headaches later

 

[DeathByTray]

FordGT90Concept
Thanks but why is that any better than my table? I just can't see the advantage.

Price Table:

id|a5850|a5870|date
1|400|300|NOW()
2|405|305|NOW()

Card table

id|model|vram
1|5850|1024
2|5870|1024

My layout gives me more columns while your layout gives me more rows. Excuse my ignorance buy why is the latter better?

W1zzard
Good to know, will change it asap.

 

[FordGT90Concept]

For example, you want to find all cards worth $300, instead of searching one table like:

SELECT price, gfx_id FROM GfxCardPrice WHERE price = 300

...you would have to do...

SELECT a5850, a5870 FROM price where a5850 = 300 OR a5870 = 300

For every additional column in the table organized in that way, your query gets longer and longer (slower and slower). The query would also have to be updated every time a new column is added. Where as the "right" way of doing it, the same query would always work so long as `price` and `gfx_id` exists.

Moreover, how will you handle blanks when there is no price change at this time on a specific card? It would probably be the same, wouldn't it? For example:

id|a5850|a5870|date
1|400|300|NOW()
2|405|305|NOW()
3|425|305|NOW()

The two 305s should be normalized but because of the way the table is laid out, there's no way to normalize it meaning the table will always have wasted space.

 

[Deleted member 3]

in my opinion it is good practice to put field values into ' even if they are numbers

Didn't we have a discussion about this some weeks ago? Integer between quotes > ODBC no likey. Obviously this doesn't apply here but it was a fair example of why it is actually wrong.

 

[FordGT90Concept]

Putting " ' ` around numbers treats it as a string rather than a numeric value. Some interpreters don't care, some do, as Dan pointed out.

 

[DeathByTray]

Thanks for the explanation but I'm still missing the aha experience, I just can't get it into my head. Anyway, I will take your advice and maybe I'll get it along the way.

 

[FordGT90Concept]

I'll expand my previous example with all the columns you inserted data into previously:

SELECT a5970, a5870, a5850, a5830, a5770, a5750, a5670, a480, a470 FROM price WHERE a5970 = 300 OR a5870 = 300 OR a5850 = 300 OR a5830 = 300 OR a5770 = 300 OR a5750 = 300 OR a5670 = 300 OR a480 = 300 OR a470 = 300

...compared to...

SELECT price, gfx_id FROM GfxCardPrice WHERE price = 300

If you had to had 6870 later, for example, you'd have to modify your query to:

SELECT a6870, a5970, a5870, a5850, a5830, a5770, a5750, a5670, a480, a470 FROM price WHERE a6870 = 300, a5970 = 300 OR a5870 = 300 OR a5850 = 300 OR a5830 = 300 OR a5770 = 300 OR a5750 = 300 OR a5670 = 300 OR a480 = 300 OR a470 = 300

...when again, the same code above would still work...

SELECT price, gfx_id FROM GfxCardPrice WHERE price = 300

That should make it eaiser to see how much work you are saving yourself down the road.


If you had to change it from 300 to something else, you only have to edit it one value versus 9+ values.

 

[DeathByTray]

Mhh.. yes, not sure if I would ever use it that way though. I'd much rather get all prices from a specific day but if I'm not mistaken, I'd still have to select all the different cards.

If I take your example, I'd get lots of prices from different dates. I guess you could further specify the exact date, so it would be alright.

SELECT price, gfx_id FROM GfxCardPrice WHERE price = 300

I'm seeing the light, I think.

 

[W1zzard]

GROUP BY gfx_id

look it up in the mysql docs

or SORT BY `date` DESC LIMIT 1 to get the most recent one

 

[DeathByTray]

Roger, redoing my database then.

 

[DeathByTray]

It's me again. Haven't had much time but I just finished implementing all the changes. Everything works perfectly, there are just two cosmetic things that bother me.

1)

while ($e <= $g)
	{
	$stuffit = "INSERT INTO	price (gfxid,price,dateadd) VALUES (".$gfxid[$e].",".$gpuprize[$e].",CURDATE())";
	mysql_query($stuffit) or die(mysql_error());
	$e++;
	}

As you see, I need a loop for every query and I'm not sure how much that hampers the performance or if it's negligible. Is it possible to put everything into one query? I guess not


2) How do I reset the ID count apart from readding the column?

 

[FordGT90Concept]

1) It shouldn't be a problem unless $g is really high. The PHP server will kill any script that takes longer than X number of seconds to execute. If it works, it should be fine but that isn't a query I'd run very frequently because it will obviously make the database bigger.

2) I don't think there is anyway to change the index number on a column marked as a primary index. Every entry is unique and reseting the number would damage the integrity of the database. It won't hurt anything to leave it like that.

 

[DeathByTray]

Alright, will come back when I run into my next problem!