CODEWITHSUNDEEP
phpmysqlsql
Mimi
Mimi

Reputation: 125

bind prepared result not return output

I am trying to rewrite a sql query to a prepared statement. This query is working fine, and giving me the correct output:

<?php
    $sql = "SELECT * FROM stores ORDER BY RAND ( ) LIMIT 3";
    $res = $mysqli->query($sql);
    //print($res);
    if ($res->num_rows > 0) {
    // output data of each row
    while($row = $res->fetch_assoc()) {
        echo "id: " . $row["id"]. "<br>" . 
             "Headline: " . $row["head"]. "<br>".
             "Description: " . $row["desc"]. "<br>";      
    }
} else {
    echo "0 results";
}
?>

I tried to rewrite the above code to the code below, but I am not getting any output. Is there something I am doing totally wrong?

Updated Code

    <?php include 'dbconnection.php' ?>

<?php
error_reporting(E_ALL); ini_set('display_errors', 1);

if (mysqli_connect_errno()) { echo "Error: no connexion allowed : " . mysqli_connect_error($mysqli); }

$query = " SELECT id, headline, description FROM stores WHERE id=? AND headline=? AND description=? ";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("iss",$id, $head, $desc); /* make sure that $id, $head, $desc are defined and that $id deserves a 'd' an not a 'i' */

$results = $stmt->execute();
$stmt->bind_result($id, $head, $desc); /* make sure you use all cols as you used SELECT (*) */
$stmt->store_result();

if ($stmt->num_rows > 0) {
while($stmt->fetch()){
    echo"[ $id -> $head -> $desc ]<br />";
  }
}
else
{ echo"[ no data ]"; }

?>

Upvotes: 0

Views: 74

Answers (4)

prashant
prashant

Reputation: 21

<?php

// '?' placeholders for variables
$stmt = $mysqli->prepare("SELECT * FROM stores WHERE id=? AND headline=? AND description=?");

/* Bind result */
$stmt->bind_param($id, $head, $desc);

// Execute prepared statement 
$stmt->execute();

/* Fetch values for each row */
$result = $stmt->get_result()

while ($row = $result->fetch_object()) {
    $result[] = $row;       
}    
echo $result;

// Close statement                        
$stmt->close();

// Close connection
$mysqli->close(); 

?>

Upvotes: -1

OldPadawan
OldPadawan

Reputation: 1251

EDIT : after more investigation, as there are actually 2 pages, and SELECT (on 2nd page) needs last inserted id from DB from page1 I updated answer :

<?php

/* this code is for insert.php */

error_reporting(E_ALL); ini_set('display_errors', 1);

$host = ""; /* your credentials here */
$user = ""; /* your credentials here */
$pwd = ""; /* your credentials here */
$db = ""; /* your credentials here */

/* store in PHP variables */

$head = $_POST["head"]; /* you can also perfom some checking on this data coming from user */
$desc = $_POST["desc"]; /* you can also perfom some checking on this data coming from user */
$place = $_POST["place"]; /* you can also perfom some checking on this data coming from user */

echo"[ $head / $desc / $place ]"; /* just checking values */

/* connexion to db */
$mysqli = mysqli_connect("$host", "$user", "$pwd", "$db");

if (mysqli_connect_errno()) { echo "Error: no connexion allowed : " . mysqli_connect_error($mysqli); }

$query = " INSERT INTO `stores` (`headline`, `description`, `place`) VALUES (?, ?, ?) "; /* make sure all columns of DB match ! */

$stmt = $mysqli->prepare($query); /* prepare query */

$stmt->bind_param("sss", $head, $desc, $place); /* bind param will sanitize */

print_r($stmt->error_list); /* any error ? */
print_r($stmt->get_warnings());
print_r($stmt->error);

if (!$stmt->execute()) { echo $stmt->error; } else { echo"[ successful insert ? -> true ]"; 

$last_id = $stmt->insert_id; echo"[ last ID ? -> $last_id ]"; echo"<a href=\"select_shuffle.php?id=$last_id\">GO TO SELECT/SHUFFLE PAGE</a>"; /* in order to get last inserted ID that will be needed next page */

/* header("Location: select_shuffle.php?id=$last_id"); -> can be performed only if no output before, otherwise you'll an error */ }

?>

then, select page :

<?php

/* this code is for select_shuffle.php */

error_reporting(E_ALL); ini_set('display_errors', 1);

$host = ""; /* your credentials here */
$user = ""; /* your credentials here */
$pwd = ""; /* your credentials here */
$db = ""; /* your credentials here */

/* store in PHP variable */

$id = $_GET["id"]; /* you can also perfom some checking on this data (is numeric ? (int) ?) */

echo"[ last insert ID -> $id ]"; /* just checking value */

// connexion to db
$mysqli = mysqli_connect("$host", "$user", "$pwd", "$db");

if (mysqli_connect_errno()) { echo "Error: no connexion allowed : " . mysqli_connect_error($mysqli); }

$query = " SELECT * FROM stores WHERE id=? ";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("i",$id); /* here we make use of $var ID */

$results = $stmt->execute();
$stmt->bind_result($col1, $col2, $col3); /* make sure you use all cols as you used SELECT (*) */
$stmt->store_result();

if ($stmt->num_rows > 0) {
while($stmt->fetch()){
echo"[ $col1 -> $col2 -> $col3 ]<br />"; /* here its echo'd but you do whatever you need */
}
}
else
{ echo"[ no data ]"; }

?>

Upvotes: 2

luckyguy73
luckyguy73

Reputation: 1939

in your first statement you are missing the closing parenthesis )

$stmt = $mysqli->prepare("SELECT * FROM stores WHERE id = ? AND headline = ? AND description = ?");

in the 4th statement you are missing the ending semi-colon ;

$result = $stmt->get_result();

see if that helps :)

Upvotes: 0

RoboNoob
RoboNoob

Reputation: 121

Use PDO Luke!

$b=$pdo->prepare(" SELECT FROM `table` WHERE `val1`=:val1 AND `val2`=:val2 ");
$b->bindParam(":val1",$val1);
$b->bindParam(":val2",$val2);
$b->execute();

Upvotes: 0

Related Questions