14 Hours Later and Hating Life - Please help

[jdlev]

Pulling information from the database is a piece of cake, but updating it is a pain in the arse.  I have tried everything, and simply cannot get this thing to work.

 

The concept of what I am trying to do is rediculously simple.  You have a matrix.  For each time slot there are 16 places available.  Currently under each time slot is a table that has 16 "OPEN" slots for people to make a reservation.  Each "OPEN" is a hyperlink.  They click it, it takes 'GET's the information to a form, where all they have to do is enter their name and hit submit.  When they hit submit, an update form goes into action (well, its supposed to)...it looks at the column and updates one of the open spaces with their name.  And you're done.  Problem is, it won't freakin update!

 

Here's my code.  Any help is really, really appreciated.  I've been at this all freakin day, and it has frustrated the heck out of me.  Thanks!

 

<?php

 

$name = $_GET['Name'];

$time = $_GET['Time'];

 

$con = mysql_connect("localhost","root","password");

if (!$con)

{

die ('Could not connect sucka! ' . mysql_error());

}

 

mysql_select_db("my_db", $con);

 

mysql_query("UPDATE my_db SET '$time' = '$name' WHERE '$time' = 'OPEN' LIMIT 1");

mysql_close($con);

header("location: index.php");

exit();

 

I am running IIS 5 on advanced server 2000, and really think it has something to do with the ability to write to the database in the file permissions.  However, file permissions have always been a bit baffling to me.  THanks again

[kevin_newbie]

Jdlev,

 

I noticed in the query you " SET '$time' = '$name' ". What is the name of your database table column name? Because $time is the get value of that variable not the name of the column. So it should be column_name = '$name' and so on.

[DavidAM]

kevin has a good point.  Or are the "16 Slots" time slots and you are passing the column name in the $time variable.

 

The second point I see is UPDATE my_db SET ... is my_db actually the name of the table you are trying to update?

[jdlev]

Jdlev,

 

I noticed in the query you " SET '$time' = '$name' ". What is the name of your database table column name? Because $time is the get value of that variable not the name of the column. So it should be column_name = '$name' and so on.

 

The name of the column is labeled A-Z, and the equivalent of the $time variable.  The time slots correspond with the columns A-Z.  So column A would be 8:00am, B would be 8:30am, and so on.  The $name is simply the name of the individual who will replace the first slot equivalent to 'OPEN' that the update statement finds. 

 

Hope that helps?  Thanks!

[jdlev]

I almost think this hast to be a permissions error, even through I am using the root user for script purposes. It looks like the script is processing the name, and attempts to update, but for whatever reason can't.  Can I add some code to the script that will throw off the error and point me in the right direction?  Or tell me if the UPDATE query is successful?

 

I just tried altering the UPDATE syntax, and changed '$time' to 'A' just to see if it would work, and that didn't work either.

[DavidAM]

at the top of your script add

error_reporting(E_ALL);
ini_set("display_errors", 1);

This should cause all PHP errors to be output.  It will not output mySql errors so change the UPDATE code to:

 

if (! mysql_query("UPDATE my_db SET '$time' = '$name' WHERE '$time' = 'OPEN' LIMIT 1")) echo mysql_error();

post any errors that are displayed so we can see what is happening.

[jdlev]

at the top of your script add

error_reporting(E_ALL);
ini_set("display_errors", 1);

This should cause all PHP errors to be output.  It will not output mySql errors so change the UPDATE code to:

 

if (! mysql_query("UPDATE my_db SET '$time' = '$name' WHERE '$time' = 'OPEN' LIMIT 1")) echo mysql_error();

post any errors that are displayed so we can see what is happening.

 

 

Here's what I got!

 

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 ''database' SET 'C' = 'me' WHERE 'C' = 'OPEN' LIMIT 1' at line 1

[jdlev]

Do you need to use a select statement before an update statement?

 

What if the database and table are named the same thing, could that cause a problem?

[DavidAM]

No, you don't have to SELECT before you UPDATE.  I don't think using the same name for the database and table is a problem.  If it is a reserved word, you'll have to put it in backticks; i.e. UPDATE `database` SET $time ...

 

The error message indicates that your UPDATE statement has the word "database" just before the SET phrase.  Is the code you showed exactly what you are running or did you try to simplify it?  Try changing the update to:

 

$sql = "UPDATE my_db SET '$time' = '$name' WHERE '$time' = 'OPEN' LIMIT 1";
echo $sql . "\n";
if (! mysql_query($sql)) echo mysql_error();

That will output the sql statement before sending it to the server.  Post the result and let's see what we're telling the server to do.  You might also cut-and-paste this part of the code exactly as is without the connection specifics (login, password, etc).  I think we are loosing something in the translation.

[jdlev]

No, you don't have to SELECT before you UPDATE.  I don't think using the same name for the database and table is a problem.  If it is a reserved word, you'll have to put it in backticks; i.e. UPDATE `database` SET $time ...

 

The error message indicates that your UPDATE statement has the word "database" just before the SET phrase.  Is the code you showed exactly what you are running or did you try to simplify it?  Try changing the update to:

 

$sql = "UPDATE my_db SET '$time' = '$name' WHERE '$time' = 'OPEN' LIMIT 1";
echo $sql . "\n";
if (! mysql_query($sql)) echo mysql_error();

That will output the sql statement before sending it to the server.  Post the result and let's see what we're telling the server to do.  You might also cut-and-paste this part of the code exactly as is without the connection specifics (login, password, etc).  I think we are loosing something in the translation.

 

I setup a whole other table and tested it.  It was a combination of the fact that the table was the same name as the db and that i had some bad syntax.

 

I have another issue.  For whatever reason, when it imports the names of individuals into the database, it gets their full name, however, when you click on the persons name, the get function is supposed to put their full name in the URL - it doesn't.  It only puts their first name in there?  How do I put their full name in there? So instead of "myaddress.php?name=Bob", I get "myaddress.php?name=Bob+Saget"

 

Thanks for all the help so far, I think this is the last step!

[DavidAM]

Yeah, you have to watch out for reserved words and ambiguous names.  Always try to use different names for database, tables, columns, etc.

 

On the other thing, it sounds like you are retrieving the full name from the database, and only part of it is showing up in the link.  In the example you gave "myaddress.php?name=Bob" instead of "myaddress.php?name=Bob+Saget", it looks like you need to encode the name:

$name="Bob Saget";
$link = "myaddress.php?name=" . urlencode($name);

that should put the '+' in there for you.  Also, in myaddress.php you will (probably) have to decode that:

$name = urldecode($_GET['name']);

 

If this is not the problem, or doesn't solve it, post some code so we can see what is happening.