1. Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

mysql/php

Discussion in 'Programming & Webmastering' started by DeathByTray, Apr 1, 2010.

  1. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    I got stuck and can't figure out why.

    PHP:
    $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());
    Any idea?
    I first thought it was due to missing backticks in $gpuprize, added them and still not working.
     
  2. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.64/day)
    Thanks Received:
    1,383
    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.
     
  3. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    You also shouldn't have to put apostrophes around numbers:
    Code:
    $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 says thanks.
    Crunching for Team TPU
  4. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    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.
     
  5. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    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.
     
    Crunching for Team TPU
  6. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    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?
     
    Last edited: Apr 1, 2010
  7. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    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 1 "5970" 2048
    2 2 "5870" 1024


    GfxCardPrice Table:
    id gfx_id price date
    1 1 1 699 NOW()
    2 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 1 699 NOW()
    2 2 2 389 NOW()
    3 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.
     
    DeathByTray says thanks.
    Crunching for Team TPU
  8. W1zzard

    W1zzard Administrator Staff Member

    Joined:
    May 14, 2004
    Messages:
    15,317 (3.83/day)
    Thanks Received:
    12,485
    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 and digibucc say thanks.
  9. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    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 1 400 300 NOW()
    2 2 405 305 NOW()


    Card table
    id model vram
    1 1 5850 1024
    2 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.
     
  10. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    For example, you want to find all cards worth $300, instead of searching one table like:

    Code:
    SELECT price, gfx_id FROM GfxCardPrice WHERE price = 300
    ...you would have to do...

    Code:
    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 1 400 300 NOW()
    2 2 405 305 NOW()
    3 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.
     
    Last edited: Apr 2, 2010
    DeathByTray says thanks.
    Crunching for Team TPU
  11. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.64/day)
    Thanks Received:
    1,383
    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.
     
  12. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    Putting " ' ` around numbers treats it as a string rather than a numeric value. Some interpreters don't care, some do, as Dan pointed out.
     
    Crunching for Team TPU
  13. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    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.
     
  14. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    I'll expand my previous example with all the columns you inserted data into previously:
    Code:
    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...
    Code:
    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:
    Code:
    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...
    Code:
    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 says thanks.
    Crunching for Team TPU
  15. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    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.
    PHP:
    SELECT pricegfx_id FROM GfxCardPrice WHERE price 300

    I'm seeing the light, I think. :p
     
  16. W1zzard

    W1zzard Administrator Staff Member

    Joined:
    May 14, 2004
    Messages:
    15,317 (3.83/day)
    Thanks Received:
    12,485
    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 says thanks.
  17. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    Roger, redoing my database then.
     
  18. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    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)
    PHP:
    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 :p


    2) How do I reset the ID count apart from readding the column?
    [​IMG]
     
  19. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    14,764 (6.18/day)
    Thanks Received:
    4,419
    Location:
    IA, USA
    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 says thanks.
    Crunching for Team TPU
  20. DeathByTray New Member

    Joined:
    Dec 21, 2009
    Messages:
    63 (0.03/day)
    Thanks Received:
    12
    Alright, will come back when I run into my next problem!
     

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page