WEB MySQL/PHP help?

Discussion in 'OT Technology' started by Eclipsed830, Sep 28, 2007.

  1. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    Is there a way when using php forms to submit data to a certain row?

    Lets say, I have a database named 2700_bens_burger, and a table named doube_burger.

    I have the following rows: id, cheese, bacon, lettuce

    Is there a way I can get a form to submit to a specific row where the id is lets say 1?

    Here is my current syntax

    Code:
    <?php
    $con = mysql_connect("localhost","username","pass");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    
    mysql_select_db("2700_bens_burger", $con);
    
    $sql="INSERT INTO double_cheeseburger (cheese, bacon, lettuce) 
    VALUES
    ('$_POST[cheese]','$_POST[bacon]','$_POST[lettuce]')";
    
    if (!mysql_query($sql,$con))
      {
      die('Error: ' . mysql_error());
      }
    echo "1 record added";
    
    mysql_close($con)
    ?>
     
    Last edited: Sep 28, 2007
  2. 420am

    420am OT Supporter

    Joined:
    Mar 10, 2003
    Messages:
    10,936
    Likes Received:
    0
    Location:
    Madison, Wisconsin
    Here's how a script I use is setup. It grabs a variable from the url though. Hope is helps.

    Code:
    <?php
    $playlist=$_GET['playlist'];
    
    $db_name="db_name";// Database name
    
    $connection=mysql_connect("localhost","db_user","db_pass") or die("I Couldn't connect");
    
    $db=mysql_select_db($db_name,$connection) or die("I Couldn't select your database");
    
    $table="tbl"; // Table name
    
    $field="fld"; // Field name
    
    if (empty($playlist)) 
              {
                echo "Playlist entry not valid";
              }
    else
              {
                $sql="update $table set $field = '$playlist'";
                $result=mysql_query($sql,$connection) or die(mysql_error());
                mysql_close($connection)
              }
    ?>
    
     
  3. LEGbEND

    LEGbEND .

    Joined:
    Jun 26, 2001
    Messages:
    23,270
    Likes Received:
    6
    Location:
    Shitcago
    I really hope that's not the code you stick with... you need to clean that input.
     
  4. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,924
    Likes Received:
    11
    Location:
    Los Angeles
    Yes, you can use the UPDATE class in SQL to submit data to a certain ID.

    Code:
    UPDATE [i]`eclipsed380`[/i] SET [i]`bacon`[/i] = [i]'value'[/i] WHERE `id` = [i]1[/i];
    
     
  5. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    I tried this and got:

    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 '(cheese, tomatoes, lettuce) VALUES ('Cheese','','') WHERE `id`


    Code:
    <?php
    $con = mysql_connect("localhost","user","pass");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    
    mysql_select_db("2700_bens_burger", $con);
    
    $sql="update double_burger (cheese, tomatoes, lettuce)
    VALUES
    ('$_POST[cheese]','$_POST[tomatoes]','$_POST[lettuce]') WHERE `id` = 1;";
    
    if (!mysql_query($sql,$con))
      {
      die('Error: ' . mysql_error());
      }
    echo "1 record added";
    
    mysql_close($con)
    ?>
    I am getting confused I think around the $_post[cheese] things ect. :(
     
  6. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    I know, its terrible, this is my first time working with php in like 3-4 years. :o This little script will only be used like once by my teacher for a visual design class, so the script really has nothing to do with my grade, I just need it to support the rest of my project. :o
     
  7. ///M Pilot

    ///M Pilot New Member

    Joined:
    Mar 16, 2006
    Messages:
    405
    Likes Received:
    0
    Location:
    Fort Liquordale, FL
    for the love of all that is unholy..
    learn sql syntax

    :)

    update `table_name` SET `field_name` = $field_value WHERE condition

    So...

    After CLEANING your POST values.. by either stripslashes or mysql_real_escape_string (depending on your version) ..

    $cheese = mysql_real_escape_string($_POST['cheese']);
    etc...

    update `double_burger` SET `cheese` = $cheese, `lettuce` = $lettuce, `tomatoes` = $tomatoes WHERE `id` = 1;

    boom.
    done.
    over.
     
  8. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    But the thing is, those are getting submited via a form:

    Code:
    <form action="menu_testing.php" method="post">
      <input type="hidden" name="cheese" value="Cheese"/>
    <input type="image" src="../restaurants/bens_burger_shack/items/cheese.jpg" height="97" width="127" border="0" alt="Cheese">
    </form>
     
  9. retorq

    retorq What up bitch??

    Joined:
    Dec 14, 2006
    Messages:
    6,061
    Likes Received:
    0
    Location:
    Mohave Desert
    Hence the $_POST['cheese'] variables ...
     
  10. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    Code:
    <?php
    $con = mysql_connect("localhost","eclipsed830","password");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    
    mysql_select_db("2700_bens_burger", $con);
    
    $sql="update `double_burger` SET `cheese` = $cheese, `lettuce` = $lettuce, `tomatoes` = $tomatoes WHERE `id` = 1
    $cheese = mysql_real_escape_string($_POST['cheese'])
    $lettuce = mysql_real_escape_string($_POST['lettus'])
    $tomatoes = mysql_real_escape_string($_POST['tomatoes'])";
    
    if (!mysql_query($sql,$con))
      {
      die('Error: ' . mysql_error());
      }
    echo "1 record added";
    
    mysql_close($con)
    ?>
    
    Yeah I did that, but now I get: Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /var/www/html/school/2700/php/menu_testing.php on line 20

    (line 20 is $cheese = mysql_real_escape_string($_POST['cheese']) )
     
  11. retorq

    retorq What up bitch??

    Joined:
    Dec 14, 2006
    Messages:
    6,061
    Likes Received:
    0
    Location:
    Mohave Desert
    That SQL update and following line is wrong.

    I'm pretty sure you want that "; at the end of the SQL line, then each of those declaration lines will need the ; at the end .. also those go before the SQL line ...
     
  12. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    Like this??

    Code:
    <?php
    $cheese = mysql_real_escape_string($_POST['cheese']);
    $lettuce = mysql_real_escape_string($_POST['lettuce']);
    $tomatoes = mysql_real_escape_string($_POST['tomatoes']);
    
    $con = mysql_connect("localhost","eclipsed830","pass");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    
    
    mysql_select_db("2700_bens_burger", $con);
    
    $sql="update `double_burger` SET `cheese` = $cheese, `lettuce` = $lettuce, `tomatoes` = $tomatoes WHERE `id` = 1";
    
    if (!mysql_query($sql,$con))
      {
      die('Error: ' . mysql_error());
      }
    echo "1 record added";
    
    mysql_close($con)
    ?>

    I get:

    Warning: mysql_real_escape_string(): Access denied for user: '[email protected]' (Using password: NO) in /var/www/html/school/2700/php/menu_testing.php on line 11

    Warning: mysql_real_escape_string(): A link to the server could not be established in /var/www/html/school/2700/php/menu_testing.php on line 11

    Warning: mysql_real_escape_string(): Access denied for user: '[email protected]' (Using password: NO) in /var/www/html/school/2700/php/menu_testing.php on line 12

    Warning: mysql_real_escape_string(): A link to the server could not be established in /var/www/html/school/2700/php/menu_testing.php on line 12

    Warning: mysql_real_escape_string(): Access denied for user: '[email protected]' (Using password: NO) in /var/www/html/school/2700/php/menu_testing.php on line 13

    Warning: mysql_real_escape_string(): A link to the server could not be established in /var/www/html/school/2700/php/menu_testing.php on line 13
    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 ' `lettuce` = , `tomatoes` = WHERE `id` = 1' at line 1
     
  13. Nakon

    Nakon Guest

    put the escape call after the db connection call.
     
  14. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    Code:
    <?php
    
    
    $con = mysql_connect("localhost","eclipsed830","password");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    
    
    mysql_select_db("2700_bens_burger", $con);
    
    $sql="update `double_burger` SET `cheese` = $cheese, `lettuce` = $lettuce, `tomatoes` = $tomatoes WHERE `id` = 1";
    
    
    $cheese = mysql_real_escape_string($_POST['cheese']);
    $lettuce = mysql_real_escape_string($_POST['lettuce']);
    $tomatoes = mysql_real_escape_string($_POST['tomatoes']);
    
    if (!mysql_query($sql,$con))
      {
      die('Error: ' . mysql_error());
      }
    echo "1 record added";
    
    mysql_close($con)
    ?>
    
    gets me

    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 ' `tomatoes` = WHERE `id` = 1' at line 1
     
  15. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,924
    Likes Received:
    11
    Location:
    Los Angeles
    Post your database schema, dude.
     
  16. Nakon

    Nakon Guest

    now move the escape calls to before the point you set the $sql variable. do I have to write it out for you??
     
  17. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    I'm so confused. :rofl: I've never really done a php/sql script before, never took any classes etc. :wtc::wtc:
     
  18. Nakon

    Nakon Guest

    Code:
    <?php
    
    if (!mysql_connect("localhost","eclipsed830","password"))
    {
        die('Could not connect: ' . mysql_error());
    }
    
    mysql_select_db("2700_bens_burger");
    
    $cheese = mysql_real_escape_string($_POST['cheese']);
    $lettuce = mysql_real_escape_string($_POST['lettuce']);
    $tomatoes = mysql_real_escape_string($_POST['tomatoes']);
    
    $sql= "UPDATE `double_burger` SET `cheese` = '".$cheese."', `lettuce` = '".$lettuce."', `tomatoes` = '".$tomatoes."' WHERE `id` = 1";
    
    if (!mysql_query($sql))
    {
        die('Error: ' . mysql_error());
    }
    echo "updated the record 1";
    
    mysql_close();
    
    ?>
    
    
    .
     
  19. retorq

    retorq What up bitch??

    Joined:
    Dec 14, 2006
    Messages:
    6,061
    Likes Received:
    0
    Location:
    Mohave Desert
    You're kidding??

    ;)

    Everyone has to start somewhere ...
     
  20. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    hmmm, ok that somewhat works. But the issue with this is, if I have something in the previous field. (lets say tomatoes, says tomatoes) and I add cheese, it adds cheese to the database, but deletes tomatoes. Is there anyway around that?
     
  21. Nakon

    Nakon Guest

    like adding a new row? what exactly are you trying to do here?
     
  22. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    http://eclipsed830.com/school/2700/php/menu_testing.php

    Each image is a form, and the image is the submit button, with a hidden field for that image.

    IE: Here is cheese and tomatoes:

    Code:
    <form action="menu_testing.php" method="post">
      <input type="hidden" name="cheese" value="Cheese"/>
    <input type="image" src="../restaurants/bens_burger_shack/items/cheese.jpg" height="97" width="127" border="0" alt="Cheese">
    </form>
    Code:
    <form action="menu_testing.php" method="post">
      <input type="hidden" name="tomatoes" value="Tomatoes"/>
    <input type="image" src="../restaurants/bens_burger_shack/items/tomatoes.jpg" height="109" width="123" border="0" alt="Tomatoes">
    </form>
    Right now when I click on cheese (one of the working ones) it adds cheese to the row with id = 1, which is fine, because this script will only be used once when my teacher grades it.

    But then if I want to add tomatoes, and click on a tomatoe to add it, it deletes the cheese out of the database, and just keeps the tomatoe.
     
  23. Nakon

    Nakon Guest

    change your html code to:
    Code:
    <form action="menu_testing.php" method="post">
    <input type="hidden" name="type" value="cheese"/>
    <input type="image" src="../restaurants/bens_burger_shack/items/cheese.jpg" height="97" width="127" border="0" alt="Cheese">
    </form>
    
    and your php backend to:
    Code:
    <?php
    
    if (!mysql_connect("localhost","eclipsed830","password"))
    {
        die('Could not connect: ' . mysql_error());
    }
    
    mysql_select_db("2700_bens_burger");
    
    $addon = mysql_real_escape_string($_POST['type']);
    
    $sql= "UPDATE `double_burger` SET `".$addon."` = '".$addon."' WHERE `id` = 1";
    
    if (!mysql_query($sql))
    {
        die('Error: ' . mysql_error());
    }
    echo "updated the record";
    
    mysql_close();
    
    ?>
    
    Your problem was that every time the form was submitted, there was only one value for the submission (cheese, tomato, etc.). This caused the query to use null for the other fields in the row. This will allow the code to select the field to update and include the value with the same variable.
     
    Last edited by a moderator: Sep 28, 2007
  24. Eclipsed830

    Eclipsed830 Active Member

    Joined:
    Jul 7, 2005
    Messages:
    39,507
    Likes Received:
    0
    Location:
    San Francisco/Cleveland
    Ok, I'm now getting: Error: Unknown column '' in 'field list'
     
  25. retorq

    retorq What up bitch??

    Joined:
    Dec 14, 2006
    Messages:
    6,061
    Likes Received:
    0
    Location:
    Mohave Desert
    ...
     

Share This Page