[RESOLVED] First PHP to MySQL error

[AKSailor]

My helpful friends,
I have diligently read the entire Newbie forum without finding a solution to this brain-dead-simple problem. I am trying to enter information into a MySQL database that I have created. Five fields, the first being an ID field, so no entry from my php script.

I have been following an online tutorial, "PHP/MySQL Tutorial" and simply cannot get this problem resolved. The error I continuously receive after using my browser to call the php page is "Parse error: syntax error, unexpected ',' in [the file name] on line 16" Help me fix this and I'll stop bothering you for a bit!

The "mysql_connect" was built by the server software of my ISP, so I'm taking that as OK. For security, I've replaced my database name and passwords. Line 16 is the $query line.

The PHP code I'm using:

<?php
		$dbh=mysql_connect ("localhost", "XXXXXX",
"XXXXXXXX") or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("XXXXXXXX");
$query = "INSERT INTO RideShareInfo VALUES (",'John Smith','04-05-2010','04-06-2010','9073455633')"; 
?>

I appreciate your help.

[halojoy]

you have doublequote in your query (",
probably it is to be two single quotes
because this would make the data to add an empty string= ''

The prefered dummy insert for AUTO_INCREMENT field like 'id' is NULL

<?php
/* original
$query = "INSERT INTO RideShareInfo VALUES (",'John Smith','04-05-2010','04-06-2010','9073455633')"; 
*/

//my corrected suggestion
$query = "INSERT INTO RideShareInfo VALUES ('','John Smith','04-05-2010','04-06-2010','9073455633')"; 

//what I should really use
$query = "INSERT INTO RideShareInfo VALUES (NULL, 'John Smith', '04-05-2010', '04-06-2010', '9073455633')"; 

?>

[bradgrafelman]

What you should really do in all INSERT queries is 1) list the columns, and 2) omit AUTO_INCREMENT columns altogether. In other words, your INSERT queries should look something like:

INSERT INTO myTable (col1, col2, col3) VALUES ('val1', 'val2', 'val3')

[AKSailor]

I hope you all can remember what it was like to be a newcomer and how grateful you were for good help. Please accept my thanks.

I have not been successful with the two methods above. I tried replacing the double quote with a single, called the script, then checked the result by logging on to my database and checking to see if I had any rows in the table. [That seems right to me, anyway, but please let me know if there's another way to check. On "phpMyAdmin", I got a green check with the words, "MySQL returned an empty result set (i.e. zero rows)." I interpret that to mean that the php script had not entered data.

The logon script was built by the server: it's right. Any repair or check I do on the database table seems to show it's OK. The problem must be my scripting, therefore.

Non-working script change 2:

$query = "INSERT INTO RideShareInfo (col2, col3, col4, col5) VALUES ('John Smith','04-05-2010','04-06-2010','9073455633')";

I did not list column 1 since I thought that column 1 was my ID column and was already populated. Am I correct?

For what it's worth, I am using GoLive CS2 in the "Source" mode to write scripting. I've had no trouble creating and maintaining a static website.

[bradgrafelman]

AKSailor wrote:

I did not list column 1 since I thought that column 1 was my ID column and was already populated. Am I correct?

Yes and no. Any columns you omit from an INSERT query will be filled in with their default values. For AUTO_INCREMENT columns, this causes a new id to be generated and used for that row. This is the same behavior as when you insert a NULL value for that column, but like I said.. it's recommended to just leave the id column out of the query altogether.

Also note that I was using "col1", etc. as placeholders; you actually have to fill in the column names from your table.

I recommended you use the column list because I think it's a better coding practice. If I gave you a query that said:

INSERT INTO myTable VALUES (3, 4, "Hello World", NULL)

can you even begin to tell me which data is going where? To do so, you'd have to first look up the column list of the table in question and hope that it hasn't been altered/re-ordered since the above query was written.

If it has been, then you're out of luck. If I had listed the columns in the query, however, you would not only have been able to easily determine which column the data is being inserted into, but the query would continue to work if new columns were added in the beginning/middle of the table (or if the columns had been re-arranged).

Long story short: update the query above with your actual column names and it should work.

Also, as for the data... what column type are columns 3 and 4 there? Since it looks like you're storing a date, why not use the DATE column type?

[AKSailor]

Hello, and thank you for the speedy response. I made the changes you suggested. I changed the field type on two of the fields to DATA columns, as you suggested.

Here's the new and improved scripting. I am sorry to say that I have not been successful.

$query = "INSERT INTO RideShareInfo ('Contact Name','Travel Date','Return Date','Contact Information') VALUES ('John Smith','04-05-2010','04-06-2010','9073455633')";
?>

I understand your comments about listing the column headers: simple and obviously a better idea that I'll use.

[halojoy]

First, in the column names list you do not quote 'names'

Second, if all the 4 fields are VARCHAR, CHAR or TEXT
then is good .... the VALUES should be 'quoted'

But if some of those fields are INT, INTEGER, then you do not quote number values

If those 4 fields are all VARCHAR, CHAR or TEXT then this would work:

<?php

$query = "INSERT INTO RideShareInfo (Contact Name, Travel Date, Return Date, Contact Information) 
VALUES ('John Smith', '04-05-2010', '04-06-2010', '9073455633')";

$result = mysql_query( $query );
if(mysql_error())
    exit($query.'<br />'.mysql_error()):
else
    echo 'Successful INSERT!';

?>

If you use such mysql_error check, then MySQL will give you info about if and what went wrong
and maybe you can fix it yourself
🙂

[bradgrafelman]

Couple more comments:

I don't have access to a MySQL server right now, but I'm 99% sure that column names with spaces in them must be surrounded with the appropriate delimiter. For MySQL, you need to use backticks ("" - on the same key as the "~" tilde :p). For example, [b]Contact Name[/b] should be [b]Contact Name`[/b] instead. It's for this reason that most DBA's use short column names and avoid things like spaces or other special characters/names (e.g. you should avoid using anything on MySQL's list of Reserved Words as an identifier).

Also, you might want to take a look at the MySQL manual page for the DATE column type I linked you to above; the data in these columns must be entered in a certain format, namely YYYY-MM-DD.

[AKSailor]

I copied your recommended text into the php script. I ran the script. I received this response: Parse error: syntax error, unexpected T_VARIABLE in /home/whsyc02/public_html/trial.php on line 16.

This is a copy of the script below the logon info:

$query = "INSERT INTO RideShareInfo (Contact Name, Travel Date, Return Date, Contact Information) 
VALUES ('John Smith', '04-05-2010', '04-06-2010', '9073455633')"; 

$result = mysql_query( $query ); 
if(mysql_error()) 
    exit($query.'<br />'.mysql_error()): 
else 
    echo 'Successful INSERT!';

I tried this script with and without the double quotes around the "INSERT" info, and neither worked.

I've searched the phpMyAdmin and have not found a location to implement an error-finding routine. I'll keep looking and appreciate your help.

[bradgrafelman]

One problem I notice is that you have a colon instead of a semicolon on this line:

exit($query.'<br />'.mysql_error()):

You're still missing the backticks around the column names in the SQL query as well.

Also, when posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

[AKSailor]

1. How do I call up MySQL_error? I'll do it and will work on my own to solve my php issue but am not sure how.

2. Here is the code I used that does not work, yet. I hope I got the "backticks" right this time since I've never used them before. I hope that I correctly used the board's bbcode tags. To make your help difficult to provide seems insult on top of injury.

3. I notice that I have double quotes in several places, but are they supposed to be double quotes? Two single quotes? Double backticks? I checked and the "backtick" is on the same key as the tilde.

 
$query ="INSERT INTO RideShareInfo (`Contact Name`,`Travel Date`,`Return Date`,`Contact Information`)
VALUES ('John Smith', '2010-04-06', '2010-04-06', '9073455633')"; 

$result = mysql_query( $query ); 
if(mysql_error()) 
    exit($query.'<br />'.mysql_error());
else 
    echo 'Successful INSERT!'; 

?>

[bradgrafelman]

AKSailor wrote:

How do I call up MySQL_error

It should only be used during debugging/development, but here's a simple and fairly common way it can be used:

$result = mysql_query( $query ) or die ("Error: " . mysql_error () . "<hr>Query: $query");

That will output both the MySQL error message as well as the full query that was sent (helps to be able to visually inspect the final query string to spot errors).

AKSailor wrote:

Here is the code I used that does not work, yet. I hope I got the "backticks" right this time since I've never used them before.

Seems to be fine to me. Just as a sidenote... the backticks are actually MySQL-specific. I believe double quotes are more commonly used.

AKSailor wrote:

I notice that I have double quotes in several places, but are they supposed to be double quotes? Two single quotes? Double backticks?

Erm... take a look at each one and see what it's used for.

You say there are double quotes "in several places", though I only see two - one each to begin and end the SQL query string. Single quotes could also be used here, since you're not doing any variable interpolation... but since literal single quotes must also appear in the query, you'd have a lot of escaping to do... thus, using double quotes is more convenient here because there aren't any quotes to escape.

Not sure why you think a single double quote should be turned into two single quotes... the only instance that happened was in your first post above, probably because you misread the online tutorial.

Ditto for the double backticks... you don't have to randomly start doubling every character you use... heh.

[AKSailor]

You are spending your precious time helping someone whom you do not know and I feel it is important for me to exhaust all possible resources rather than just sit and await your help. Several sources show double quotes as being wrong and that they are often confused with two singles. I needed to clarify.

If my script looks OK, but is still not working, then my logon is suspect, right? When I upload the script to my site, I just stick it in the main site directory: nowhere special. When I call the script, I open a browser and key in the site name/script.php. I usually receive a response such as the error, but sometimes nothing. Each time I check the MySQL table, there are no rows, no entries, so I take that to mean that nothing I did was successful.

I'm thinking that I might write a script that just echos "you made it in" or some such. At least I'll know whether my logon is correct. I am at a loss if the script is correct but still not inserting data into that table.

Can you recommend a good book or other resource?

Sorry for the duration of this problem. Please accept my deep appreciation for all the time you've spent on it.

[bradgrafelman]

Do you get your "Successful INSERT!" message when you visit the page? If mysql_query() is returning TRUE for an INSERT statement, I can't see how the data wouldn't be showing up. You could also try adding this:

echo "Number of rows inserted: " . mysql_affected_rows();

[AKSailor]

Here's my final, successful script:

<?php
		$dbh=mysql_connect ("localhost", "XXXXXXXX",
"XXXXXXXXX") or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("XXXXXXXXX");
$query = "INSERT INTO RideShareInfo (`Contact Name`,`Travel Date`,`Return Date`,`Contact Information`)
VALUES ('John Smith', '2010-04-06', '2010-04-06', '9073455633')"; 

$result = mysql_query( $query ); 
if(mysql_error()) 
    exit($query.'<br />'.mysql_error());
else 
    echo 'Successful INSERT!'; 

?>

I believe that you fixed all the scripting problems during the last session, but in the end of my logon line I was missing a semicolon. I saw it (because of your training), added it, and received the coveted "INSERT successful!"

I have marked the thread as resolved. Please accept my thanks for your patience. If you were in Alaska, I'd buy you a beer.

I will be working on creating a webpage that inserts this kind of into the database table, then inserting the stored info into a webpage table, next. I'll work on it before I request any help.

Thank you for your time, patience, and expertise.