Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    May 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    mysql & php help! error!

    Hi!

    I'm doing an assignment for school and I need help finding the error in my code. It's a two part process where a php form grabs the data and inserts the result into mysql. i'll try to only post up the bits and pieces that are relevant.

    here's the form part. as far as i know this part is working fine, but it might be helpful to see it?
    Code:
    $query2 = "SELECT ticker FROM tickers ORDER BY ticker ";
    $result2 = mysql_query($query2) or die("Query failed : " . mysql_error());
    
    print("Stock Ticker:<br />");
    print("<select name=ticker>"); 
    
    while ($line2 = mysql_fetch_array($result2))   {
        print("<option value=\"$line2[0]\">$line2[0]</option>\n");
    }
    print("</select>");
    here is the result part. its linked via: <form action="transaction_added.php" method="post">
    Code:
    $ticker    = $_POST['ticker']; 
    $dataCheck = 1;
    
    if ($dataCheck > 0) 
    {
    
    /* Performing SQL SELECT query */
    $query1 = "SELECT close FROM stocks WHERE the_date = $the_date AND ticker = $ticker ";
    $price = mysql_query($query1) or die("Query failed : " . mysql_error());
    the error message i'm getting on the webpage is: Query failed : Unknown column 'JCP' in 'where clause'
    JCP is the ticker value in this case. I'm not sure why it's taking JCP to be the column name? What am I doing wrong here?

    Thanks!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,894
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Tch.

    You surely know that in PHP code, if you tried to do
    Code:
    $xyz = JCP;
    you would get an error, right?

    Because JCP is not a number, is not a string, and is not a built-in PHP keyword.

    So you would fix it by doing
    Code:
    $xyz = "JCP";
    or
    $xys = 'JCP';
    Yes?

    Guess what: SAME THING in SQL!!!

    If you do *NOT* put '...' around a value in SQL queries (in any version of SQL, not just mySQL) then SQL *MUST* treat the value as either a number *OR* a SQL keyword *OR* a field in your database table.

    So MySQL is seeing you do
    Code:
       .... AND ticker = JCP
    And it says: Well, JCP isn't a number. And I know JCP isn't a SQL keyword. So it *MUST* be the name of a column in his stocks table. But wait, there isn't any column by that name. Okay, give him an error: Unknown column 'JCP'

    The easy fix, of course, is to put '...' around the value:
    Code:
       .... AND ticker = '$ticker' ";
    (and yes, PHP will ignore those '...' and assume they are part of the string).

    That's step one.

    Step 2 is here:
    Code:
        WHERE the_date = $the_date
    Guess what? MySQL expects '...' around date values, also. And, on top of that, MySQL only understands dates in the format 'YYYY-MM-DD hh:mm:ss' (with the time part optional). So you need to read the PHP manual to figure out how to format a date like that (not hard).

    *******

    Nota bene: Actually, MySQL is flexible enough that it *CAN* accept a date as a number, but then the number must be in the format YYYYMMDDhhmmss. And that's probably harder to coax out of PHP than 'YYYY/MM/DD'.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    iamnothyper (05-04-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,894
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Forgot to mention: The comments about dates are predicated on the I VERY MUCH HOPE VALID assumption that the data type of the_date in your MYSQL table is DATE or DATETIME or TIMESTAMP. I sincerely hope you didn't fall in the trap of using an INT or VARCHAR field for a date field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,894
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    And I just want you to know that it makes me very nervous to see a programmer who is not hyper. <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    May 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    ah, i see. iunno why but i wrote another piece of code the same way and it worked so i got confused. must have overlook some subtle differences. thanks!

    and i meant 'not hyper' in a ' >___> <____< HAHAHAHAH O____O ' way. if that makes any sense.

  • #6
    New to the CF scene
    Join Date
    May 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    wait come back!!

    that stopped the JCP error but when I do this to check

    Code:
    print("\t Price of Stock: $price <br>\n");
    I get "Price of Stock: Resource id #4" ?
    How do i get it to print the actual price?

    i'm also getting the same problem for $fullname and $buysell. i thought setting them equal to query stores the value?

    and i'm getting this error message at the bottom of my screen.

    Query failed : 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 'id #4,1,1)' at line 2

  • #7
    New to the CF scene
    Join Date
    May 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    AH, here's the code part for buysell and fullname

    Code:
    $query2 = "SELECT CONCAT(fname,' ',lname)FROM clients WHERE client_ID = '$client_ID' ";
    $fullname = mysql_query($query2) or die("Query failed : " . mysql_error());
    
    $query3 = "SELECT CASE buy WHEN 0 THEN 'Sell' WHEN 1 THEN 'Buy' END FROM transactions ";
    $buysell = mysql_query($query3) or die("Query failed : " . mysql_error());

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,894
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Since you likely got the answer in another forum, there's not much point in answering you now.

    But there's no way that code made any sense, at all.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •