Retreving data from the database based on select function in php

[sabarinadh.ch]

I am newbie in php and i have code for retreving data from the website where the $id=id now i want to retreive the data based on the code Select * from users where cat1=cat1 and cat2=cat2 and also show the data according to the id in decrement order. i paste my code below can any one solve this . Thanks in advance :)

$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="forum_question"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>

Here is the sample code now i want to add some extent to select from where cat1=cat1

[Squidge]

I would suggest not using * but actually refering to the table columns. This is concidered a security risk.

Also note that mysql is not longer maintained, you woul dneed to start looking at MySQLi or PDO. If you are just starting I wuld suggest doing this sooner than later :)

// get value of id that sent from address bar
$id=$_GET['id'];
$sql="SELECT * FROM $tbl_name WHERE id='$id'";

This little bit here, leaves you wide open for attack, as you should sainitise the data to prevent attacks.

To retrive the data and sort by DESC:

ORDER BY id DESC

I hope that helps

[sabarinadh.ch]

MySQLi

can u made it for me i want to show the data where name=name and class=class and have to show that data order by id DESC

[Squidge]

<?php
// Using PDO

$tbl_name = "forum_question";

$pdo = new PDO('mysql:dbname=test;host=localhost', '', ''); // first '' is user, second '' is password
$stmt = $pdo->prepare("SELECT * FROM $tbl_name where id= :id ORDER BY id DESC");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);

?>

Your above SQL using PDO, you would then step through the data or display it with something like:

while($row = $stmt->fetch())
{
    echo $row['TABLE_COLUMN_1'] . " " . $row['TABLE_COLUMN_2'];
}

'

$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);

This sanitises the int PDO::PARAM_INT.

This is order by the id

ORDER BY id

So to do the same using name and class you should be able to do this.

[sabarinadh.ch]

Thanks for your help in the above code we provide only

id= :id
but i need more likd name= :name cls= :cls how we add those functions to this code
and the id is in DESC or not because the latest post will show first and also

[Squidge]

The :id is a placer. Using this is more secure, and allows you to clean the data. Because you are GETting data from the URL this could have been altered or used for SQL injection.

So we use a place holder.

You can use the same for name and cls, although this

$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);

is specific to getting the data from the URL

[sabarinadh.ch]

selecting multiple rows using AND function . so is this is a right code to select multiple rows and show the data according to id in DESC... When i am inserting data id is the only way to get the latest data if it is not possible i have time and date label when i am inserting data

<?php
// Using PDO
$tbl_name = "forum_question";
$pdo = new PDO('mysql:dbname=test;host=localhost', '', ''); // first '' is user, second '' is password
$stmt = $pdo->prepare("SELECT * FROM $tbl_name where id= :id AND cls= :cls ORDER BY id DESC");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
?>

[Squidge]

Where is the :cls coming from? is this in the url also?

[sabarinadh.ch]

no that is in the table

[Squidge]

how are you defining the :cls variable?

Is this from $_POST, $_GET data?

Else I do not understand how you expect to use it in your SQL stmt?

[sabarinadh.ch]

actually the thing is i am using a form to post data into database now i want to retreive those data from database. Here when the user clicks on the cls the webpage shows all the data related to cls in the datbase table. The data is inserted through id increment function so the DESC id will be shows at the top of the webpage as the new post

[Squidge]

$stmt = $pdo->prepare("SELECT * FROM $tbl_name where id= :id AND cls= :cls ORDER BY id DESC");

This is a select statement not an insert. So you would not need the id= :id
$stmt = $pdo->prepare("SELECT * FROM $tbl_name where cls= :cls ORDER BY cls DESC");

$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);

This would need to be changed as you are using POST data

$stmt->bindParam(':cls', $_POST['cls']);