Hi all
Been messing with this now for the past 3 hours or so (:
As it stands, I have about 5 different SQL statements querying the same DB from an old build, I would like to some how set up a prepared statement which I’ve been trying as shown below.
No errors, just no content.
<?php
#set the database accesss information as constants
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 'root');
DEFINE ('DB_NAME', 'test');
/* create a new server connection */
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
/* Create a prepared statement */
if($stmt = $mysqli -> prepare("
SELECT venue_id
FROM tbl_venues
WHERE category_id=?")) {
// Bind parameters
$stmt -> bind_param("s");
// Execute it
$stmt -> execute();
// Bind results
$stmt -> bind_result($result);
// Fetch the value
$stmt -> fetch();
printf("%s is a good venue \
");
// Close statement
$stmt -> close();
}
// Close connection
$mysqli -> close();
?>
Can anybody help?
Hope this makes sense, thanks in advance, Barry
Update:
Warning: Wrong parameter count for mysqli_stmt::bind_param() in …
Warning: printf() [function.printf]: Too few arguments in …
Ok, getting closer 
Seems to be working.
db_connection…
$category_id = "alternative";
/* Create a prepared statement */
if($stmt = $mysqli -> prepare("
SELECT venue_id,
address
FROM tbl_venues
WHERE category_id=?")) {
// Bind parameters for markers
$stmt -> bind_param("s", $category_id);
// Execute query
$stmt -> execute();
// Bind result variables
$stmt -> bind_result($venue_id, $address);
// Fetch the value
while ($stmt->fetch())
{
printf("%s %s\
", $venue_id, $address);
}
// Close statement
$stmt -> close();
}
// Close connection
$mysqli -> close();
How do I break the results up into the different category_id’s so I can display different sections spread about the page?
EXAMPLE
// Fetch the values - category_id (electro)
while ($stmt->fetch())
{
printf("%s %s\
", $venue_id, $address);
}
// Fetch more values - category_id (disco)
while ($stmt->fetch())
{
printf("%s %s\
", $venue_id, $address);
}
// Fetch even more values - category_id (house)
while ($stmt->fetch())
{
printf("%s %s\
", $venue_id, $address);
}
Do I need to add some sort of array?
many thanks, Barry
Sorry for bumping my thread spent a lot of hours trying to figure this out, any help much appreciated.
Thanks, Barry
How do I break the results up into the different category_id’s so I can display different sections spread about the page?
You can try something like this (untested):
<?php
error_reporting(-1);
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 'root');
DEFINE ('DB_NAME', 'test');
function getVenueInfo( $category_id ) {
static $array;
if( !$array ) {
$array = array();
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) {
die('Connect Failed: ' . $mysqli->connect_errno);
}
$sql = 'SELECT * FROM tbl_venues';
if( $result = $mysqli->query($sql) ) {
while ($row = $result->fetch_assoc()) {
$array[$row['category_id']][] = $row;
}
}
}
return $array[$category_id];
}
?>
<!DOCTYPE html>
<html>
<body>
<?php foreach( getVenueInfo('alternative') as $info ) {
echo $info['venue_id'] . ' - ' . $info['address'];
} ?>
<?php foreach( getVenueInfo('electro') as $info ) {
echo $info['venue_id'] . ' - ' . $info['address'];
} ?>
</body>
</html>
Thanks centered effect, I think this is the kind of thing I’m looking for 
Just wondering, are you suggesting to replace all my code with your code ?
Many thanks, Barry
Seems to be working quite good, thanks CE.
Just wondering what the differences are between my code and yours?
Is this a mixture of OO and Procedural style ?
Should I be worried about any sort of SQL attacks?
Many thanks, Barry
No problem. If you still want to use prepared statements and you don’t want to pull all that data into memory, you can do this:
*using PDO - mysqli doesn’t have a nice way to do this
$dsn = 'mysql:dbname=test;host=localhost';
$user = 'root';
$password = 'root';
try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
function getVenueInfo( $category_id, $dbh ) {
$array = array();
$sql = 'SELECT venue_id, address FROM tbl_venues WHERE category_id = :category_id';
$sth = $dbh->prepare($sql);
$sth->bindParam(':category_id', $category_id);
$sth->execute();
while( $row = $sth->fetchAll(PDO::FETCH_ASSOC) ) {
$array[$category_id] = $row;
}
return $array[$category_id];
}
The first method pulled ALL information from the DB table once and upon calling a parameter, that array was returned. Here, each query is done as needed and using only the selected columns.
The first method pulled ALL information from the DB table once and upon calling a parameter
Thanks CE,
I think the first option is just what I need, haven’t really used PDO and giving a couple of errors, I was just wondering what the differences where, something maybe I can look at when I get a bit more experience.
This has really saved me a lot of duplicate code and should speed things up dramatically.
Couple of questions:
- so this is Procedural style?
- do I need to escape any of the code, attacks?
Barry
Encase you’re wondering,
PDO code gave the following errors:
Warning: Missing argument 2 for getVenueInfo(), called in …
Notice: Undefined variable: dbh in …
Fatal error: Call to a member function prepare() on a non-object in …
But no errors once I removed the output code:
<?php foreach( getVenueInfo('alternative') as $info ) {
echo $info['venue_id'] . ' - ' . $info['address'];
} ?>
<?php foreach( getVenueInfo('electro') as $info ) {
echo $info['venue_id'] . ' - ' . $info['address'];
} ?>
Does this need to be different with the prepared statement approach?
Is the PDO way better?
The issues you were having with option 2 was you weren’t passing the $dbh handle to the function. I hoped you were reading and understanding what was given instead of just copying and pasting.
Here’s a different (optimized?) version of option 2:
$dsn = 'mysql:dbname=test;host=localhost';
$user = 'root';
$password = 'root';
function db() {
global $dsn, $user, $password;
try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
return $dbh;
}
function getVenueInfo( $category_id ) {
$sql = 'SELECT venue_id, address FROM tbl_venues WHERE category_id = :category_id';
$sth = db()->prepare($sql);
$sth->bindParam(':category_id', $category_id);
$sth->execute();
return $sth->fetchAll(PDO::FETCH_ASSOC);
}
Now to your questions:
- so this is Procedural style?
- do I need to escape any of the code, attacks?
- PDO isn’t. mySQLi has both procedural and OOP ways of using the functions - see here: http://www.php.net/manual/en/mysqli.query.php
// OOP
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli->query($sql);
// Procedural
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
mysqli_query($link, $sql);
- Yes, you still need to sanitize the data (for the second option more, not so much for the first).
Is the PDO way better?
I like PDO because I use different DB’s like SQLite, but it is up to you and here is a helper: http://www.php.net/manual/en/mysqli.overview.php
Much appreciated centered effect, was hoping you’d get back
I was understanding the first example but wasn’t sure about the PDO.
I still have the first OOP option in play and trying to get some of this code finished, very eager to update a project of mine.
Great examples and links thank you.
Will need to go away and digest this now to get a better understand of whats happening.
Will keep this post updated if I have any further issues.
Only recently switched from mysql - mysqli… lots of updating to do.
Cheers, Barry