SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statements

    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.

    Code PHP:
    <?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 \n");
     
           // 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 ...
    Last edited by computerbarry; Jan 8, 2012 at 16:57. Reason: more information
    The more you learn.... the more you learn there is more to learn.

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, getting closer
    Seems to be working.

    db_connection....
    Code PHP:
    $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\n", $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

    Code PHP:
    // Fetch the values  - category_id (electro)
           while ($stmt->fetch())
               {
               printf("%s %s\n", $venue_id, $address);
     
               }

    Code PHP:
    // Fetch more values - category_id (disco)
           while ($stmt->fetch())
               {
               printf("%s %s\n", $venue_id, $address);
     
               }

    Code PHP:
    // Fetch even more values  - category_id (house)
           while ($stmt->fetch())
               {
               printf("%s %s\n", $venue_id, $address);
     
               }

    Do I need to add some sort of array?

    many thanks, Barry
    The more you learn.... the more you learn there is more to learn.

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for bumping my thread spent a lot of hours trying to figure this out, any help much appreciated.

    Thanks, Barry
    The more you learn.... the more you learn there is more to learn.

  4. #4
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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):
    Code PHP:
    <?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>

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    The more you learn.... the more you learn there is more to learn.

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    The more you learn.... the more you learn there is more to learn.

  7. #7
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Code PHP:
    $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.

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    1) so this is Procedural style?
    2) 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:

    Code PHP:
    <?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 more you learn.... the more you learn there is more to learn.

  9. #9
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code PHP:
    $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:
    1) so this is Procedural style?
    2) do I need to escape any of the code, attacks?
    1. 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
    Code 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);

    2. 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

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    The more you learn.... the more you learn there is more to learn.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •