Login | Register | FAQ
Anonymous

Retrieve data from the database -error

+ Post a reply

Page 1 of 1


Retrieve data from the database -error

by Arun » Sat Jul 14, 2012 7:41 am

I have created 2 file 1) list.php & another person.php

Code for list.php

<html>
<head>
<title>Retrieve data from the database</title>
</head>
<body>

<ul>

<?php
// Connect to database server
mysql_connect("localhost", "testadmin", "admin245") or die (mysql_error ());

// Select database
mysql_select_db("test") or die(mysql_error());

// SQL query
$strSQL = "SELECT * FROM contacts ORDER BY fname DESC";

// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($strSQL);

// Loop the recordset $rs
while($row = mysql_fetch_array($rs)) {

// Name of the person
$strName = $row['fname'] . " " . $row['lname'];

// Create a link to person.php with the id-value in the URL
$strLink = "<a href = 'person.php?id = " . $row['id'] . "'>" . $strName . "</a>";



// List link
echo "<li>" . $strLink . "</li>";

}

// Close the database connection
mysql_close(); ?>

</ul>
</body>
</html>
Its giving me result i.e.

Arun Chavan
Akshay Kumar
with hyper link

But if I click hyper link it give error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/aruncrj9/public_html/arunchavan.com/person.php on line 25
Return to the list

My Code for person.php is

html>
<head>
<title>Retrieve data from database</title>
</head>
<body>

<dl>

<?php


// Connect to database server
mysql_connect("localhost", "testadmin", "admin245") or die (mysql_error ());

// Select database
mysql_select_db("test") or die(mysql_error());

// Get data from the database depending on the value of the id in the URL



$strSql = "SELECT * FROM contacts WHERE id = ". $_GET['id'];
$rs = mysql_query($strSql);

// Loop the recordset $rs
while($row = mysql_fetch_array($rs)) {

// Write the data of the person

echo "<dt>Phone:</dt><dd>" . $row['phone1'] . "</dd>";


}

// Close the database connection
mysql_close();
?>
</dl>
<p><a href="list.php">Return to the list</a></p>

</body>

</html>
User avatar

Arun

  • Posts: 4
  • Joined: Sat Jul 14, 2012 7:09 am

Re: Retrieve data from the database -error

by atik » Sat Jul 14, 2012 8:04 am

Make sure you are getting right query. Add a print statement before running query. Also use mysql_error() function which might give you some idea about the error.
Code: Select all
$strSql = "SELECT * FROM contacts WHERE id = ". $_GET['id'];
print $strSql;
$rs = mysql_query($strSql) or die(mysql_error());


If query looks fine, then copy the query from browser and run through sql window or phpmyadmin or some other tool and see if you are getting correct data.
User avatar

atik

  • Posts: 472
  • Joined: Mon Oct 17, 2011 4:55 pm

Re: Retrieve data from the database -error

by Arun » Sat Jul 14, 2012 10:10 am

To Atik

Error

SELECT * FROM contacts WHERE id = 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 '' at line 1
User avatar

Arun

  • Posts: 4
  • Joined: Sat Jul 14, 2012 7:09 am

Re: Retrieve data from the database -error

by atik » Sat Jul 14, 2012 10:34 am

This clearly shows your query has error related to $_GET['id'], might be you are not getting the value of $_GET['id']

Code: Select all
$strSql = "SELECT * FROM contacts WHERE id = ". $_GET['id'];
print $strSql;


Can you please tell me what the print $strSql value? it should be just before the error message.
User avatar

atik

  • Posts: 472
  • Joined: Mon Oct 17, 2011 4:55 pm

Re: Retrieve data from the database -error

by Arun » Sat Jul 14, 2012 11:43 am

To Adik

No value befor this message

It only give following message

SELECT * FROM contacts WHERE id = 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 '' at line 1 valve
User avatar

Arun

  • Posts: 4
  • Joined: Sat Jul 14, 2012 7:09 am

Re: Retrieve data from the database -error

by atik » Sat Jul 14, 2012 3:20 pm

Arun wrote:To Adik

No value befor this message

It only give following message

SELECT * FROM contacts WHERE id = 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 '' at line 1 valve

SELECT * FROM contacts WHERE id =

That is the result of print statement, which you have mentioned in your post. That means when you are running the query, you are actually passing that sql statement. Look, isn't it invalid? you don't have any id after = sign. Than means $_GET['id'] is empty. The way you are obtaining $_GET['id'] is by passing query string, so actually $row['id'] is also empty. To debug this, i would suggest you to print the $row array. Check the below code -

Code: Select all
while($row = mysql_fetch_array($rs)) {
print_r($row);
// Name of the person
$strName = $row['fname'] . " " . $row['lname'];

// Create a link to person.php with the id-value in the URL
$strLink = "<a href = 'person.php?id = " . $row['id'] . "'>" . $strName . "</a>";



// List link
echo "<li>" . $strLink . "</li>";

}

Notice, i have added a new line print_r($row) in while loop, now check the output and see which elements the $row array has, specially check for element id and its value.
User avatar

atik

  • Posts: 472
  • Joined: Mon Oct 17, 2011 4:55 pm

Re: Retrieve data from the database -error

by aardwolf » Tue Aug 14, 2012 6:27 pm

Code for list.php

<html>
<head>
<title>Retrieve data from the database</title>
</head>
<body>

<ul>

<?php
// Connect to database server
mysql_connect("localhost", "testadmin", "admin245") or die (mysql_error ());

// Select database
mysql_select_db("test") or die(mysql_error());

// SQL query
$strSQL = "SELECT * FROM contacts ORDER BY fname DESC";

// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($strSQL);

// Loop the recordset $rs
while($row = mysql_fetch_array($rs)) {

// Name of the person
$strName = $row['fname'] . " " . $row['lname'];

// Create a link to person.php with the id-value in the URL
$strLink = "<a href = 'person.php?id = " . $row['id'] . "'>" . $strName . "</a>";



// List link
echo "<li>" . $strLink . "</li>";

}

// Close the database connection
mysql_close(); ?>

</ul>
</body>
</html>
Its giving me result i.e.

Arun Chavan
Akshay Kumar
with hyper link

But if I click hyper link it give error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/aruncrj9/public_html/arunchavan.com/person.php on line 25
Return to the list

My Code for person.php is

html>
<head>
<title>Retrieve data from database</title>
</head>
<body>

<dl>

<?php


// Connect to database server
mysql_connect("localhost", "testadmin", "admin245") or die (mysql_error ());

// Select database
mysql_select_db("test") or die(mysql_error());

// Get data from the database depending on the value of the id in the URL



$strSql = "SELECT * FROM contacts WHERE id = ". $_GET['id'];
$rs = mysql_query($strSql);

// Loop the recordset $rs
while($row = mysql_fetch_array($rs)) {

// Write the data of the person

echo "<dt>Phone:</dt><dd>" . $row['phone1'] . "</dd>";


}

// Close the database connection
mysql_close();
?>
</dl>
<p><a href="list.php">Return to the list</a></p>

</body>

</html>

The $_GET['id'] in the person.php file was empty, that caused the next query to be empty. The $_GET['id'] was empty because the id never got passed from the list.php file properly. There were extra blanks inside the double-quotes when the $strLink value was assigned in the list.php file. That prevented the URL from correctly passing the id value as those embedded blanks were also passed thru the URL.

Remove the extra blanks from the values inside the double quotes, in your list.php file,
change this code:
$strLink = "<a href = 'person.php?id = " . $row['id'] . "'>" . $strName . "</a>";

into this code:
$strLink = "<a href = 'person.php?id= " . $row['id'] . "'>" . $strName . "</a>";

Note that the blank after the ?id and before the = has been removed.
That will allow the person.php file to properly find a value for the $_GET['id'] and the files will work as intended. I tested your code on my database with the modification I have shown here and it works.

Last edited by aardwolf on Thu Aug 16, 2012 8:14 am, edited 1 time in total.

User avatar

aardwolf

  • Posts: 1
  • Joined: Tue Aug 14, 2012 4:15 pm

Re: Retrieve data from the database -error

by XainPro » Wed Aug 15, 2012 4:51 am

i think the problem is with your query replace your this line
$strSql = "SELECT * FROM contacts WHERE id = ". $_GET['id'];
with mine
$strSql = "SELECT * FROM contacts WHERE id = $_GET['id']";

and why loop there is only 1 record ?


$row = mysql_fetch_array($rs))

// Write the data of the person

echo "<dt>Phone:</dt><dd>" . $row['phone1'] . "</dd>";
User avatar

XainPro

  • Posts: 3321
  • Joined: Fri Feb 17, 2012 8:10 pm


+ Post a reply

Page 1 of 1