Debug Question

[ivrylineslead]

Hi All-

Just started with PHP/MySQL... Ive been having a problem with this certain script, and can't figure out this particular error. Probably something stupid, but I've been staring at it for hours.

---------

<?php
$date = $_POST['date'];
$venue = $_POST['venue'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$with = $_POST['with'];
$details = $_POST['details'];
$website = $_POST['website'];

// Insert a row of information into the table "example"
mysql_query("INSERT INTO shows
(date, venue, address, city, state, zip, with, details, website) VALUES('$date', '$venue', '$address', '$city', '$state', '$zip', '$with', '$details', '$website' ) ")
or die(mysql_error());

// Make a MySQL Connection
$query = "SELECT * FROM shows";

$result = mysql_query($query) or die(mysql_error());

//while($row = mysql_fetch_array($result)){
echo "<table border='1'>";
echo "<tr> <th>Date</th> <th>Venue</th> <th>With</th> <th>Details</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['date'];
echo "</td><td>";
echo $row['venue'] . " - " . $row['city'] . ", " . $row['state'];
echo "</td><td>";
echo $row['with'];
echo "</td><td>";
echo $row['details'];
echo "</td></tr>";
}
echo "</table>";
?>

------------
Produces the following 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 'with, details, website) VALUES('', '', '', '', '', '', '', '', '' )' at line 2

[eelixduppy]

Welcome to WebmasterWorld!

Try changing this line:

mysql_query("INSERT INTO shows(date, venue, address, city, state, zip, with, details, website) VALUES('$date', '$venue', '$address', '$city', '$state', '$zip', '$with', '$details', '$website' ) ") or die(mysql_error());

To this:

mysql_query("INSERT INTO shows
(date, venue, address, city, state, zip, `with`, details, website) VALUES('$date', '$venue', '$address', '$city', '$state', '$zip', '$with', '$details', '$website' ) ")
or die(mysql_error());

Also, make sure that you escape all your variables:

$date = [url=http://us3.php.net/manual/en/function.mysql-real-escape-string.php]mysql_real_escape_string[/url]($_POST['date']);
$venue = mysql_real_escape_string($_POST['venue']);
$address = mysql_real_escape_string($_POST['address']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zip = mysql_real_escape_string($_POST['zip']);
$with = mysql_real_escape_string($_POST['with']);
$details = mysql_real_escape_string($_POST['details']);
$website = mysql_real_escape_string($_POST['website']);

Good luck!

[ivrylineslead]

wow, that worked. thanks a lot.. whats the insight in putting thought accent marks around that particular with variable?

[eelixduppy]

with is a reserved word [dev.mysql.com] in MySQL.

If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it.

The identifier quote character is the backtick (‘`’):

[dev.mysql.com...]

Best of luck ;)

[ivrylineslead]

Leads into my next question. Essentially what this script does is take information from an html form, and fill the table in mySQL. At first I used this code:

-----
// Insert a row of information into the table "example"
mysql_query("INSERT INTO shows
(date, venue, address, city, state, zip, `with`, details, website) VALUES('$date', '$venue', '$address', '$city', '$state', '$zip', '$with', '$details', '$website' ) ")
or die(mysql_error());
------

which worked fine, but would insert null default/null data at the top of the table, because I'm assuming data would get entered into the database as soon as the page was loaded, and the user didn't submit data throuhg the form yet. I came up with this code to try to solve this problem, but the entries are not getting submitted to the database.

------
<input name="submit" type="submit" />
<?php
//checks if submit button is pushed
$onsubmit = $_POST['onsubmit'];
//if true^^^^then update topics where the sticky column = 1
if($onsubmit){
// Insert a row of information into the table "example"
mysql_query("INSERT INTO shows
(date, venue, address, city, state, zip, `with`, details, website) VALUES('$date', '$venue', '$address', '$city', '$state', '$zip', '$with', '$details', '$website' ) ")
or die(mysql_error());
echo"Topic Stickied Successfully"; }
?>

</form>
--------

Any suggestions would be greatly appreciated!

Bill

[eelixduppy]

Do something like this to get your desired effect:

<?php

if(isset($_POST['onsubmit'])) {
//connect to mysql server
//select db

$date = mysql_real_escape_string($_POST['date']);
$venue = mysql_real_escape_string($_POST['venue']);
$address = mysql_real_escape_string($_POST['address']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zip = mysql_real_escape_string($_POST['zip']);
$with = mysql_real_escape_string($_POST['with']);
$details = mysql_real_escape_string($_POST['details']);
$website = mysql_real_escape_string($_POST['website']);

mysql_query("INSERT INTO shows
(date, venue, address, city, state, zip, `with`, details, website) VALUES('$date', '$venue', '$address', '$city', '$state', '$zip', '$with', '$details', '$website' ) ")
or die(mysql_error());
echo "Topic Stickied Successfully"; 
} else {
//print form
}
?>

Something like that :)

[henry0]

In addition to eelixduppy great help
I would like stating that instead of escaping a reserved word it would make a lot of sense to change it by a non reserved one before encountering a possible escape omission in further script