CODEWITHSUNDEEP
phpmysqlpdo
aphextwig
aphextwig

Reputation: 553

Converting mysql into PDO format

I'm trying to convert some old php mysql code into PDO format but am stuck. I've looked at other posts on here but can't quite figure it out.

This is the old code:

<?php

if (isset($_POST['query'])) {
    // Connect to database
    mysql_connect("localhost", "xxxxx", "xxxxx");
    mysql_select_db("xxxxx");

    // Retrieve the query
    $query = $_POST['query'];

    // Search the database for all similar items
    $sql = mysql_query("SELECT * FROM articles WHERE title LIKE '%{$query}%'");
    $array = array();

    while ($row = mysql_fetch_assoc($sql)) 
    {
     $array[] = $row['title'];
    }

    // Return the json array
    echo json_encode($array);

}

?>

And this is what I've managed to do but think there's something wrong in the "while" part.

<?php

if (isset($_POST['query'])) {
require( "config.php" );
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );

// Retrieve the query
$query = $_POST['query'];

// Search the database for all similar items
$sql = "SELECT * FROM articles WHERE title LIKE '%{$query}%'";
$array = array();

while ($row = $sql->fetchAll()) {
    $array[] = $row['title'];
}

// Return the json array
echo json_encode($array);

}

?>

Upvotes: 1

Views: 200

Answers (4)

Justin Iurman
Justin Iurman

Reputation: 19016

You are trying to call fetchAll on "sql" which is a string.

Now, you could use query but i suggest you to use prepare instead (for security reason, because you insert POST data).

$q = $conn->prepare("SELECT * FROM articles WHERE title LIKE CONCAT('%', ? ,'%')");
$q->execute(array($query));

// result contains all returned data
$result = $q->fetchAll();

// or row by row
while($row = $q->fetch())

Upvotes: 3

Lajos Arpad
Lajos Arpad

Reputation: 76436

Try to run this:

$rows = $conn->prepare("SELECT * FROM articles WHERE title LIKE ?")->execute(array('%'.$query.'%'))->fetchAll();

while($row = $rows->fetch()) {
    // TODO: Parse the rows
}

Also, try not to use * in your queries, that's not the best practice, it is better to use a column list instead separated by commas, as you do not necessary need to load the values of all columns. select * is less scalable and it might be the source of security vulnerabilities, like accidentally loading the inappropriate column and passing its value to the inappropriate place.

Upvotes: 0

Hackerman
Hackerman

Reputation: 12305

Try somehing like this:

<?php

 if (isset($_POST['query'])) {
 require( "config.php" );
 $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );

 // Retrieve the query
 $query = $_POST['query'];

 //Build Query - Search the database for all similar items
 $sql = "SELECT * FROM articles WHERE title LIKE '%{$query}%'";
 $array = array();
 $sth = $conn->query($sql);
 $result = $sth->fetchAll();

 foreach($result as $row){
 $array[] = $row['title'];
 }

 // Return the json array
 echo json_encode($array);

}

?>

=========Updated Answer========

//Better alternative
 $query = $_POST['query'];
 $sql = "SELECT * FROM articles WHERE title LIKE CONCAT('%', ? ,'%')";
 $sth = $con->prepare($sql);
 $sth->bind_param("s", $query);
 $sth->execute();
 $result = $sth->fetchAll();

 foreach($result as $row){
 $array[] = $row['title'];
 } 
 // Return the json array
 echo json_encode($array);

PS: Best practice is to stick with prepared statements and execute for increased security.

Upvotes: 0

Rick Hoving
Rick Hoving

Reputation: 3575

From PHP.net

foreach ($conn->query($sql) as $row) {

Upvotes: 1

Related Questions