Displaying result from MySQL ? (Help !)

[BlaZe]

I'm currently learning how to insert data in MySQL database and fetching data from MySQL database.

I have learned how to insert, but displaying the result from MySQL is giving me some problems.

This is the command to create the tables. I'm confused with the id structure and I guess thats the main root of the error I'm getting at the output.

$query="CREATE TABLE post (id int NOT NULL auto_increment,title text(300) NOT NULL,cover text NOT NULL,info text NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))"; 

Now the php code to fetch the data from the mysql db is this

$db = mysql_connect($hostname,$username,$password);
if(!$db){
    echo 'Failed to connect to database. Check your info';
    }
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM post";
/*$result = mysql_query($query);*/
$result = mysql_query($query) or die(mysql_error());
mysql_close();

$id = $_GET['id'];

$title=mysql_result($result,$id,"title");

echo 'ID='.$id.' - Title='.$title.''; 

and whenever I go like preview.php?id=1 or ..?id=2 to ..?id=4, the results are as I wanted. But whenever I put preview.php?id=5 or preview.php?id=6 I get this error

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 5 on MySQL result index 5 in C:\xampp\htdocs\post\preview.php on line 87

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 5 on MySQL result index 5 in C:\xampp\htdocs\post\preview.php on line 88

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 5 on MySQL result index 5 in C:\xampp\htdocs\post\preview.php on line 89
ID=5Title= 

Also, this is the exact data in my mysql database displayed by a PHP script.

ID	Title	Delete
2	This Is Title	Delete	Preview
3	This Is Title	Delete	Preview
4	This Is Title	Delete	Preview
5	This Is Title	Delete	Preview
6	This Is Title	Delete	Preview

Remember I just have 5 rows filled. The row with ID 1 is deleted by me as a part testing the script. I don't think that it'd be a problem.

BlaZe Reviewed by BlaZe on 23rd Nov 2011. Displaying result from MySQL ? (Help !) I'm currently learning how to insert data in MySQL database and fetching data from MySQL database. I have learned how to insert, but displaying the result from MySQL is giving me some problems. This is the command to create the tables. I'm confused with the id structure and I guess thats the main root of the error I'm getting at the output. $query="CREATE TABLE post (id int NOT NULL auto_increment,title text(300) NOT NULL,cover text NOT NULL,info text NOT NULL,PRIMARY KEY (id),UNIQUE id Rating: 5

[BlaZe]

I modified the code and currently, this is working fine. I know its a lame and poor code, but its still doing what it should do.

<?php    $id = $_GET['id'];
$db = mysql_connect($hostname,$username,$password);
if(!$db){
    echo 'Failed to connect to database. Check your info';
    }
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM post";
/*$result = mysql_query($query);*/
$result = mysql_query($query) or die(mysql_error());
$title = mysql_result($result,--$id,"title");
$cover = mysql_result($result,$id,"cover");
$info = mysql_result($result,$id,"info");

echo 'ID='.++$id.' - Title='.$title.'';

mysql_close();?>

If anyone can still clean the code and make it efficient, please feel free to do so.

[t3od0r]

<?php    
$id = $_GET['id'];
$db = mysql_connect($hostname,$username,$password);
if(!$db){
    echo 'Failed to connect to database. Check your info';
}
@mysql_select_db($database) or die( "Unable to select database");
if(isset($id)&&is_int($id)) $where = " where id='".mysql_real_escape_string($id)."'";
$query = mysql_query("SELECT * FROM post".$where) or die(mysql_error());
$row = mysql_fetch_assoc($query);

$title = $row['title'];
$cover = $row['cover'];
$info = $row['info'];

echo 'ID='.$id.' - Title='.$title.'';

mysql_close();
?>

[BlaZe]

Oh nice t3od0r you even secured it

But unfortunately I'm getting an error

Notice: Undefined variable: where in C:\xampp\htdocs\post\user.php on line 23

[Halcyon]

<?php

$db = mysql_connect($hostname,$username,$password);
if(!$db){
    echo 'Failed to connect to database. Check your info';
    }
@mysql_select_db($database) or die( "Unable to select database");
$id = $_GET['id'];

$query = "SELECT * FROM post WHERE id = $id";
$result = mysql_query($query) or die(mysql_error());
mysql_close();

$data = mysql_fetch_array($result);

/* Debug statements
 * echo "<pre>";
 * print_r($data);
 * echo "</pre>";
 */

echo "<br />ID: " . $data['id'];
echo "<br />Title: " . $data['title'];
echo "<br />Cover: " . $data['cover'];
echo "<br />Info: " . $data['info'];
?>

Hope that helps.

Make sure you add proper conditional statements before making the query, t3o did that

[BlaZe]

@Halcyon,

It worked !

Thanks a million ! and thanks to t3od0r too I'm gonna now use his technique to secure the code hehe
====
EDIT:
I have a question, how did the $id take the value inside a string ?
I mean $query = "SELECT..... id = $id" how did the $id variable take the value ? Its inside a string and normally it'd be like $query = "SELECT ... id=".$id;

? right ? ^ I did that though, but it gave me errors. Still didn't knew that the $var can take values inside a string

[Gavo]

Either should work.

echo $id;
echo "id: $id";
echo "id: ".$id; 

all correct

[Halcyon]

It's PHP buddy!

<?php
$id = 10;

echo "ID: $id"; //shows ID: 10
echo "ID: ".$id; //shows ID: 10
echo 'ID: '.$id; //shows ID: 10
echo 'ID: $id'; //shows ID: $id

?>

I hope it's clear now

[BlaZe]

o.o really didn't knew that lol

I always thought that the $var converts to a normal text inside " " or ' '

lol

Thanks guys Thanks for everything