Prepared Statements


The codes we see here:

Are they in oop style (pdo) or procedural style ?

Those examples are mysqli and oop.
They are not pdo.

mysqli can be either procedural or oop.

1 Like


Let us say my mysql tbl called “users” looks like this:

Name Gender City Country
UI Man Male Munich Germany
Daniella Female New York USA

Q1. Let us say, I want the whole table displayed. How would I code it using PREPARED STATEMENTS ?
I tried it like this but I reckon there is a shorter way. How would the short version code look like ?


ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');

$link = mysqli_connect("localhost", "my_user", "my_password", "world");       

    /* check connection */
    if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());

    /* create a prepared statement */
    if ($stmt = mysqli_prepare($link, "SELECT Name, Gender, City, Country FROM users")) {

    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "ss", $name, $gender, $city, $country);

    /* execute query */

    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $name, $gender, $city, $country);

    /* fetch value */

    printf("%s is %s gender and lives in city %s from country %s\n", $name, $gender, $city, $country);

    /* close statement */

/* close connection */


Look at the procedural style on this link, where I am getting the code from and modifying here and there:

No guarantee I will find the answers on these videos but still downloading them to see what I can learn from them:

Seems like Prepared Statements have weaknesses. Downloading to find-out:

yes, with PDO. see

1 Like

For me (and I might be wrong, it wouldn’t be the first time), the whole point of prepared statements is to ease the passing of parameters into the query. As you want to retrieve and display the entire table, there aren’t really any parameters to pass, so a prepared statement isn’t really helping a great deal in this specific situation.

Again, I don’t use mysqli, but I don’t see why you don’t get an error in this line:

/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "ss", $name, $gender, $city, $country);

First because your query doesn’t actually have any markers, and second because you define two in your “types” parameter, but then supply four. Presumably mysqli just ignores the last two.

I’m also not sure why you execute it immediately after you open the if() clause having called mysqli_prepare(), then bind the parameters (which is surely too late, once it’s been executed?) and then execute it again. It’s also not going to display the entire table, because you only retrieve the first row.


four parameters declared, but only two parameter types specified.

1 Like

Yes, this a bad example where it would be more efficient to not prepare, as there are no variables in the query.

No. I meant, without PDO. Doing it procedural style with mysqli.

Well, you can knock two lines out of your code by not using bind_param() when you don’t need to, and by not executing the query twice.

1 Like

Dunno why this guy imagined that programming is their calling, but apparently they’ve got a humanitarian mindset.

They need to write essays, not programs.

  • It is evident that it’s much more desirable for them to write 100 lines of text explaining why they are unable to fix their code than actually fix one line in the code.
  • they are actively refusing to learn by experience, by running a code, seeing the outcome, fixing the code, running the code and so on.
  • their only accepted method of learning is consuming video tutorials. Although it could be good for the Sociology or Philosophy or History or anything else of that kind, one cannot learn from a video how to fix a program they have no idea of, no matter how many hours of video has been consumed.

So, @uniqueideaman, listen to a good advise: quit programming and consider to be a journalist or a bloger or a writer. You really like to write. Texts, not programs. Your progress with programming (at least judging by this topic) is rather negative. Yet looking at this topic one could tell you are quite eloquent with your speeches. Just choose another audience because programmers are a special kind, they trust the code, not words. There are fashion blogers, or food testers or other occupations that do not require hard working and concentration but only a dexterous pen. So you would like to try yourself in that area, and I can see you reaching a great success.

  1. I am crap at writing. Grammatical errors and so on.
  2. Yes, I am a fast typer and so my “paragraphs” may take you 5 mins to read & digest but it took me 2 mins to write each.
  3. I will be opening a blog soon where I will do all the writing there.
  4. You are a bigger & better writer than me when it comes to complaining.
  5. Even though I spend a lot of time complaining, at the end, I get responses and solutions one way or the other. And, from the solutions I learn things. Hence, the complaining becomes worth it at the end.
  6. Out of all the forums and out of all the programmers I have come across, you are the only person who gives a negative vibe this way trying to make me quit. While there are other negative, grumpy and downright foolish and rude programmers exist in other forums (who I have come across), not a single one told me to quit. Maybe 1 or 2 within the 6-8 mnths. Can’t remember clearly about them as I ignore them and forget them. But they were never as urging as you.
    Are you from the php competition camp ? Python ? Don’t worry, I’ll join your camp soon. Maybe around Christmas. Until then, I’m sticking to php. Therefore, you can try as much as you like but your plans would become fruitless.

Look how much I downloaded about 12hrs ago. And, all on a single subject: PREPARED STATEMENTS (mysqli). And, you are telling me to quit ? !!!

I’ll admit that I’m quite fascinated by your (dare I call it) obsession with binding statements.

My shorter way:

$stmt = $dbal->prepare("SELECT Name, Gender, City, Country FROM users");
$users = $stmt->fetchAll();
foreach($users as $user) {
    printf("%s is %s gender and lives in city %s from country %s\n", 
        $user['Name'], $user['Gender'], $user['City'], $user['Country']);

This statement “$users = $stmt->fetchAll();” is actually kind of subtle.

It gives you an ordinary array of users after which you no longer need any database type statements. The rest of the code does not need to know about mysqli_stmt_fetch($stmt); or anything else database oriented.

This in turn allows you to start separating your code. You can isolate the database queries into their own little section. And then all your listing routine needs to know is that it is given an array of users as input. Powerful concept and is reason enough to avoid using mysqli_stmt_bind_result.

In any event, enjoy your bindings.


Thank you very much for your short version! :slight_smile:
I am making note of your code to my NotePad++ to use when I migrate to PDO and OOP. I believe the style you used was OOP.
As of now, I’m still at procedural style using mysqli because all beginner php tutorials revolve around them.
That phpdelusion site, wich everyone recommends, is for intermediate level students.
I’m still a beginner.

So, if you don;t mind and when you got the time, then how about, switching your code to mysqli and procedural ? Newbies can now see how they both differ and which one looks more easier to learn. :slight_smile:

I actually used this template on my original post:

Anyway, gonna watch some youtube tutorials now on this subject. I hope they answer a lot of questions.
Thanks for your time, effort and help! I appreciate it.


Look at the img on post 11. The title of the vid on the top row of the far right column.
Is that the title of the php book that was on offer for free download yesterday by PacktPub ?
Writer’s name seems the same.
Ok, so, I missed the download link to the free ebook. But, if I get the same writers one of his vid then that is something. :wink:

Indeed you are but the projects you are attempting are advanced level stuff, so until you square that circle you are going to have trouble advancing your skills.


See Improvements To Member Registration Site Reg.php - #213 by TechnoBear

Well no. 99% of all the “beginner php tutorials” out there are crap. Some started out okay but have not been updated. Others are just plain sad. They are a complete waste of your time.

If it helps you out then consider my code to be a “beginner php tutorial” and move on. I mean seriously, is the code I have posted really more “advanced” than what you are using? Is $stmt->fetchAll() really more difficult to understand than mysql_stmt_fetch_all($stmt) ?


Let me finish learning some basics on how to get php to interact with mysql and then you’ll be surprised what I can build!
Finished 90% of all my projects so far! (member reg-login script, web proxy). Gonna start on web crawler and searchengine. Very soon :wink:

I figured I got the title of the book wrong as the vid was on prepared statements and the ebook was on basic php. I edited my previous post to you. You may check.

Why not get at least one project 100% before starting another?