PHP Question: Updating the database won't work(?)

Mega Man X · 08-07-2004, 03:13 AM

Hi there!

I need help with PHP again. I'll post all my steps, so you can understand better my background. I wanted to create a page, with one single <table> to display my list of Xbox games. That is easy to do, but I wanted to create also another page where I could update the database, without the need to fireup mysql. Here is what I did:

1 - create a database called "gamelist". In that database, I created a table with some initial values:

Code:

mysql> show tables;
+--------------------+
| Tables_in_gamelist |
+--------------------+
| xbox               |
+--------------------+
1 row in set (0.00 sec)

Code:

mysql> show columns from xbox;
+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id           | tinyint(4) |      | PRI | NULL    | auto_increment |
| title              | char(255)  |      |     |         |                |
| manufacture | char(80)   |      |     |         |                |
| genre       | char(30)   |      |     |         |                |
+-------------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Code:

mysql> select * from xbox;
+----+-------------+-------------+---------------------------+
| id | title       | manufacture | genre                     |
+----+-------------+-------------+---------------------------+
|  1 | Blood Rayne | Majesto     | 3rd Person shooter/Action |
|  2 | Halo        | Bungie      | 1st Person shooter        |
+----+-------------+-------------+---------------------------+
2 rows in set (0.00 sec)

Cool, now yo've an idea how the database is organized. Now to the coding. Here is a simple page which displays the above database results:

PHP Code:



<html> 
<body> 
<head> 
<title> Testing PHP/Apache/MySQL </title> 
</head> 
 
  <?php 
 
    $db = mysql_connect("localhost", "root"); 
 
    mysql_select_db("gamelist",$db); 
 
    $result = mysql_query("SELECT * FROM xbox",$db); 
 
     
    /* page main table starts */ 
    echo "<table width=100% border = 1>\n"; 
    echo "<tr bgcolor='green'>"; 
    echo "<td><p align='center'>id</p></td>"; 
    echo "<td><p align='center'>Title</p></td>"; 
    echo "<td><p align='center'>Manufacture</p></td>"; 
    echo "<td><p align='center'>Genre</p></td>"; 
    echo "</tr>\n"; 
 
   
    while ($myrow = mysql_fetch_row($result)) { 
        printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", 
        $myrow[0], $myrow[1], $myrow[2], $myrow[3]); 
    } 
     
    /* page main table ends */ 
    echo "</table>\n"; 
 
  ?> 
   
 
</body> 
 
</html>

That works just fine. The problem is here... I've a simple page, called update.php with one form and 3 fields, plus a submit button. Whenever I fill the fields and submit the data, mysql is not updated, not I get error messages....

PHP Code:



<html> 
<head><title> updating database </title></head> 
<body> 
 
  <?php 
 
    if ($submit){ 
 
      $db = mysql_connect("localhost", "root"); 
 
      mysql_select_db("gamelist", $db); 
 
      $sql = "INSERT INTO xbox (title, manufacture, genre) VALUES  
             ('$title', '$manufacture', '$genre')"; 
 
      $result = mysql_query($sql); 
 
      echo "Thanks!\n"; 
      
    } else { 
 
    // display form 
 
  ?> 
 
    /* simple form with some fields for title, manufacture and genre */ 
    <form method="post" action="<?php echo $PHP_SELF ?>"> 
    Title: <input type="Text" name="title"><br> 
    Manufacture: <input type="Text" name="manufacture"><br> 
    Genre: <input type="Text" name="genre"><br> 
    <input type="Submit" name="submit" value="Enter Information"> 
    </form> 
 
    <?php 
 
  } // endif 
   
    ?> 
 
</body> 
</html>

Any help with this would me immensily appreciated.

Thanks in advance!

kev82 · 08-07-2004, 03:28 AM

not sure but at a guess id say its a register_globals problem, try replacing every variable from the html form $pqr with $_POST['pqr'] ie

$submit -> $_POST['submit']
$genre -> $_POST['genre']

you can read about it here

Mega Man X · 08-07-2004, 03:42 AM

Thanks a lot kev82!.

I've tried replacing the variables, but most like I did something wrong:

PHP Code:



<html> 
<head><title> updating database </title></head> 
<body> 
 
  <?php 
 
    if ($_POST['submit']){ 
 
      $db = mysql_connect("localhost", "root"); 
 
      mysql_select_db("gamelist", $db); 
 
      $sql = "INSERT INTO xbox (title, manufacture, genre) VALUES  
             ($_POST['title'], $_POST['manufacture'], $_POST['genre'])"; 
 
      $result = mysql_query($sql); 
 
      echo "Thanks!\n"; 
      
    } else { 
 
    // display form 
 
  ?> 
 
    <form method="post" action="<?php echo $PHP_SELF ?>"> 
    Title: <input type="Text" name="title"><br> 
    Manufacture: <input type="Text" name="manufacture"><br> 
    Genre: <input type="Text" name="genre"><br> 
    <input type="Submit" name="submit" value="Enter Information"> 
    </form> 
 
    <?php 
 
  } // endif 
   
    ?> 
 
</body> 
</html>

Now I get the error: Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /srv/www/htdocs/update.php on line 14

where line 14 is:
$sql = "INSERT INTO xbox (title, manufacture, genre) VALUES
($_POST['title'], $_POST['manufacture'], $_POST['genre'])";

I've tried even replacing all variables, including $db and $result. Still no go

Mega Man X · 08-07-2004, 03:49 AM

Oh yeah, specifications about my PHP version:

PHP 4.3.4 (cli) (built: Jul 15 2004 20:16:02)
Server version: Apache/2.0.49

Charalambos · 08-07-2004, 04:13 AM

Quote:

Originally posted by Megaman X
Now I get the error: Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /srv/www/htdocs/update.php on line 14

where line 14 is:
$sql = "INSERT INTO xbox (title, manufacture, genre) VALUES
($_POST['title'], $_POST['manufacture'], $_POST['genre'])";

You get an error because in the sql statement, variables have to be between ' '.
Now either define a temp variable before the statement ($genre = $_POST['genre']) and insert that into the statement or insert the POST varibale like that into the statement
"INSERT INTO xbox (title, manufacture, genre) VALUES ('".$_POST['title']."', '".$_POST['manufacture']."', '".$_POST['genre'])."' "

kev82 · 08-07-2004, 04:50 AM

by Charalambos
You get an error because in the sql statement, variables have to be between ' '.

i think you'll find although that is a problem its not the problem as that would have caused the query to fail which wouldnt have bothered php in the slightest. the actual problem is double quotes dont evaluate arrays properly, that is why(imho) you should use sprintf to do your queries, not only does it avoid this problem but i think it makes them more readable too.

PHP Code:



 <?php 
 
    if ($_POST['submit']){ //this ones correct 
 
      $db = mysql_connect("localhost", "root"); 
 
      mysql_select_db("gamelist", $db); 
 
      $sql = sprintf("INSERT INTO xbox (title, manufacture, genre) VALUES 
             ('%s', '%s', '%s')", $_POST['title'], $_POST['manufacture'], $_POST['genre']); //now the arrays are not in double quotes 
 
      $result = mysql_query($sql); 
 
      echo "Thanks!\n"; 
      
    } else { 
 
    // display form 
 
  ?>

Mega Man X · 08-07-2004, 05:06 AM

OMG, Charalambos and kev82, you are both great at this. First, I know how difficult it is to read such a terrible written code as mine...

I've tried Charalambos method, and I got a simple parsing error instead. Still, with all my incompetence, I could not fix it. kev82's method does, works amazingly well

.

I cannot thank you guys enough. Thanks!

P.S: PHP is harder then I though it would be, but cool nonetheless

kev82 · 08-07-2004, 06:01 AM

by Megaman X
PHP is harder then I though it would be, but cool nonetheless

my first PHP thing was a table of links, just like bookmarks but you can access it from any computer/browser combination, and its still my favourite. one of the most useful things i ever wrote.

im slowly trying to re-create something like this for a set of games called suikoden but doing it (well) is a bit out of my league at the moment i need to write a set of classes to let me produce html constructs on the fly but im no good at design which is what that needs.

Mega Man X · 08-07-2004, 06:37 PM

I looked into the site. Very good idea, if I could, I'd do that with Megaman series

. But besides being also out of my league (if it's far from yours, think how far from my league that is...ghehe), I also dont have much time to play with my computer atm

Mega Man X · 08-08-2004, 12:16 PM

I've one more question though... a silly one

. How would I go by displaying the game list in an inverted order. I mean, where the newest game come on the top of the list. I believe that something should be done at this line:

PHP Code:



$result = mysql_query("SELECT * FROM xbox",$db);

at the first page, something like "WHERE id=?" maybe...

Thanks again

kev82 · 08-08-2004, 12:29 PM

the statement your looking for is order by, but the only field you have that is suitable for your purpose is id making the query

select * from xbox order by id descending

but this is a bad idea as it induces a dependance on id which i assume is to be used for joining, doing this can make migration to another db very difficult.

what you need to add is another column, something like date_purchased and then make your query

select * from xbox order by date_purchased descending

Mega Man X · 08-08-2004, 02:51 PM

Wow kev82, thanks mate!

Haven't tested it yet, but I own you a very big thanks in this thread

. Great idea about the purchased date