Ad:

PHP Discussion Thread
Unsolved
Views: 1025

Aug 6th, 2009

Query failed: You have an error in your SQL syntax; check the manual that corresponds

Expand Post »

hi i need some help someone please. i tried so many combinations of code with but not getting anywhere. I need a user to enter an id through a form, then the code should retrieve certain fields of the table and display them in a table. here is the code.

php Syntax (Toggle Plain Text)
<form method="POST" action="actioned.php">
<table>
<col span="1" align="right">
<tr>
<td><font color="blue">Membership No:</font></td>
<td><input type="text" name="id" id="id" size=50></td>
</tr>
<tr>
<td><input type="submit" value="Submit"></td>
</tr>
</table>
</form>
 
 
<?php 
// Connects to your Database 
$dbhost = "localhost";  // variable holding host information
$dbname = "sportscentre";  // variable holding the name of the database
$dbuser = "*****";  // variable holding the username of the person using database account
$dbpass = "*****";  // variable holding password of the person using database account
 
$connection = mysql_connect($dbhost, $dbuser, $dbpass)or die("Cannot connect");
 
$connection = mysql_select_db($dbname)or die("cannot select DB");
 
$id = $_POST['id']; 
 
$data = mysql_query("SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members where id = '$_post[id]'")or die(mysql_error());
$result = mysql_query($data)or die ("Query failed: " . mysql_error());
 
Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data )) 
{ 
Print "<tr>"; 
Print "<th>Title:</th><td>".$info['title'] . "</td></tr>";
Print "<tr>";
Print "<th>First Name:</th> <td>".$info['first_name'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Surname:</th> <td>".$info['surname'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 1:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 2:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>City:</th> <td>".$info['city'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Postcode:</th> <td>".$info['postcode'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Tel Number:</th> <td>".$info['tel_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Mob Number:</th> <td>".$info['mob_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Email:</th> <td>".$info['email'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Username:</th> <td>".$info['username'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Password:</th> <td>".$info['password'] . " </td></tr>"; 
} 
Print "</table>"; 
?>
<form method="POST" action="actioned.php">
<table>
<col span="1" align="right">
<tr>
<td><font color="blue">Membership No:</font></td>
<td><input type="text" name="id" id="id" size=50></td>
</tr>
<tr>
<td><input type="submit" value="Submit"></td>
</tr>
</table>
</form>


<?php 
// Connects to your Database 
$dbhost = "localhost";  // variable holding host information
$dbname = "sportscentre";  // variable holding the name of the database
$dbuser = "*****";  // variable holding the username of the person using database account
$dbpass = "*****";  // variable holding password of the person using database account

$connection = mysql_connect($dbhost, $dbuser, $dbpass)or die("Cannot connect");

$connection = mysql_select_db($dbname)or die("cannot select DB");

$id = $_POST['id']; 

$data = mysql_query("SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members where id = '$_post[id]'")or die(mysql_error());
$result = mysql_query($data)or die ("Query failed: " . mysql_error());
 
Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data )) 
{ 
Print "<tr>"; 
Print "<th>Title:</th><td>".$info['title'] . "</td></tr>";
Print "<tr>";
Print "<th>First Name:</th> <td>".$info['first_name'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Surname:</th> <td>".$info['surname'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 1:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 2:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>City:</th> <td>".$info['city'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Postcode:</th> <td>".$info['postcode'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Tel Number:</th> <td>".$info['tel_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Mob Number:</th> <td>".$info['mob_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Email:</th> <td>".$info['email'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Username:</th> <td>".$info['username'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Password:</th> <td>".$info['password'] . " </td></tr>"; 
} 
Print "</table>"; 
?>

Last edited by peter_budo; Aug 6th, 2009 at 8:01 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Similar Threads

SQL Syntax Error in MySQL
You have an error in your SQL syntax; in PHP
Questionable cookie in update in PHP
Error Help - Query fatal error in PHP
PHP Query Failing After Site Move in PHP

Newbi

Reputation Points: 13

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Aug 2009

Permalink

Aug 6th, 2009

Re: Query failed: You have an error in your SQL syntax; check the manual that corresponds

I don't know if this will solve your whole problem but one thing is staring me in the face:

php Syntax (Toggle Plain Text)
$data = mysql_query("SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members where id = '$_post[id]'")or die(mysql_error());
$result = mysql_query($data)or die ("Query failed: " . mysql_error());
$data = mysql_query("SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members where id = '$_post[id]'")or die(mysql_error());
$result = mysql_query($data)or die ("Query failed: " . mysql_error());

There should be no mysql_query() on the first line. You can also delete the or die at the end of that line. Instead, try this:

php Syntax (Toggle Plain Text)
$data = "SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members WHERE id = '$_post[id]'";
$result = mysql_query($data)or die ("Query failed: " . mysql_error());
$data = "SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members WHERE id = '$_post[id]'";
$result = mysql_query($data)or die ("Query failed: " . mysql_error());

Dukane

Reputation Points: 45

Solved Threads: 28

Posting Whiz in Training

Offline

282 posts
since Oct 2006

Permalink

Aug 7th, 2009

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Thanks Dukane i tried what u said & i got over that part of the problem but now it is giving me the abv error and wont display the information that i am asking for. The revised code below.

php Syntax (Toggle Plain Text)
<?php 
ini_set('display_errors',1); 
error_reporting(E_ALL);
// Connects to your Database 
$dbhost = "localhost";  // variable holding host information
$dbname = "sportscentre";  // variable holding the name of the database
$dbuser = "*****";  // variable holding the username of the person using database account
$dbpass = "*****";  // variable holding password of the person using database account
 
$connection = mysql_connect($dbhost, $dbuser, $dbpass)or die("Cannot connect");
 
$connection = mysql_select_db($dbname)or die("cannot select DB");
 
$id = $_POST['id']; 
 
$data = "SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members where id = '$id'";
 
$result = mysql_query($data)or die ("Query failed: " . mysql_error());
 
Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data )) 
{ 
Print "<tr>"; 
Print "<th>Title:</th><td>".$info['title'] . "</td></tr>";
Print "<tr>";
Print "<th>First Name:</th> <td>".$info['first_name'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Surname:</th> <td>".$info['surname'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 1:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 2:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>City:</th> <td>".$info['city'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Postcode:</th> <td>".$info['postcode'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Tel Number:</th> <td>".$info['tel_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Mob Number:</th> <td>".$info['mob_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Email:</th> <td>".$info['email'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Username:</th> <td>".$info['username'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Password:</th> <td>".$info['password'] . " </td></tr>"; 
} 
Print "</table>"; 
?>
<?php 
ini_set('display_errors',1); 
error_reporting(E_ALL);
// Connects to your Database 
$dbhost = "localhost";  // variable holding host information
$dbname = "sportscentre";  // variable holding the name of the database
$dbuser = "*****";  // variable holding the username of the person using database account
$dbpass = "*****";  // variable holding password of the person using database account

$connection = mysql_connect($dbhost, $dbuser, $dbpass)or die("Cannot connect");

$connection = mysql_select_db($dbname)or die("cannot select DB");

$id = $_POST['id']; 

$data = "SELECT title, first_name, surname, addr_line_1, addr_line_2, city, postcode, tel_number, mob_number, email FROM members where id = '$id'";

$result = mysql_query($data)or die ("Query failed: " . mysql_error());
 
Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data )) 
{ 
Print "<tr>"; 
Print "<th>Title:</th><td>".$info['title'] . "</td></tr>";
Print "<tr>";
Print "<th>First Name:</th> <td>".$info['first_name'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Surname:</th> <td>".$info['surname'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 1:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Address Line 2:</th> <td>".$info['addr_line_1'] . " </td></tr>"; 
Print "<tr>";
Print "<th>City:</th> <td>".$info['city'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Postcode:</th> <td>".$info['postcode'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Tel Number:</th> <td>".$info['tel_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Mob Number:</th> <td>".$info['mob_number'] . " </td></tr>"; 
Print "<tr>";
Print "<th>Email:</th> <td>".$info['email'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Username:</th> <td>".$info['username'] . " </td></tr>"; 
//Print "<tr>";
//Print "<th>Password:</th> <td>".$info['password'] . " </td></tr>"; 
} 
Print "</table>"; 
?>

Last edited by Ezzaral; Aug 7th, 2009 at 12:22 pm. Reason: Added [code] [/code] tags. Please use them to format any code that you post.

Newbi

Reputation Points: 13

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Aug 2009

Permalink

Aug 7th, 2009

Re: Query failed: You have an error in your SQL syntax; check the manual that corresponds

it says it has a problem with this line,
while($info = mysql_fetch_array( $data ))

Newbi

Reputation Points: 13

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Aug 2009

Permalink

Aug 9th, 2009

Re: Query failed: You have an error in your SQL syntax; check the manual that corresponds

This is because the variable $data contains a string which is your SQL string. The actual data is in $result. So your line should be

php Syntax (Toggle Plain Text)
while($info = mysql_fetch_array( $data ))
while($info = mysql_fetch_array( $data ))

Dukane

Reputation Points: 45

Solved Threads: 28

Posting Whiz in Training

Offline

282 posts
since Oct 2006

Permalink

Aug 10th, 2009

Re: Query failed: You have an error in your SQL syntax; check the manual that corresponds

@Dukane
You made the same error as Newbi..the correct code is below (Don't worry about it, I've made worse mistakes

PHP Syntax (Toggle Plain Text)
while($info = mysql_fetch_array($result))
while($info = mysql_fetch_array($result))

The reason for this is as Dukane explained...When you perform a query the query string is executed with the mysql_query() function. This function returns a special MySQL Result that you can then pass to mysql_fetch_array() or any other MySQL Result functions.

FlashCreations

Reputation Points: 47

Solved Threads: 47

Posting Whiz

Offline

393 posts
since Sep 2008

Permalink

Aug 12th, 2009

Re: Query failed: You have an error in your SQL syntax; check the manual that corresponds

Thank you that solved the problem

Newbi

Reputation Points: 13

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Aug 2009

Permalink

Aug 12th, 2009

Re: Query failed: You have an error in your SQL syntax; check the manual that corresponds

You're welcome! If your original problem is now solved, I ask that you mark this thread as solved.

FlashCreations

Reputation Points: 47

Solved Threads: 47

Posting Whiz

Offline

393 posts
since Sep 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: can we use multipe and in the where clause

Next Thread in PHP Forum Timeline: Two different PHP aps for the same form data.

DaniWeb Message

Cancel Changes

User Name
Password