Using PHP & MySQLi With A MySQL Database Part 2

/ PHP / by Paul Robinson / 12 Comments
This post was published back on April 29, 2010 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

If you have no idea what this tutorial is about, please go back to the previous post and read all about the basics of using MySQLi.

Inserting Data

Okay. First let’s look at inserting data into the database:

It’s actually exactly the same as selecting data. You just change the query for an INSERT query instead. Deleting & updating queries are done in exactly the same way, again just changing the query. Just in case you’ve never seen those before, let’s go over them.

Deleting Data

Here is how to delete data:

Here is how to update data:


Determining The Affect

Something you’d probably want to do after a insert, delete or update is determine how many rows were affected. Whether it’s for the user, to keep a log or for debug purposes, it’s probably a good idea to know how to find out.

Miscellaneous Features

Finally let’s go over some of the miscellaneous, but exceptionally useful methods provided by MySQLi. First off is error checking.

There is also a row counting. It will give you the number of rows returned by a select query.

That’s about all there is to it. Hopefully these two posts have helped you learn the basics of using MySQLi to connect to your MySQL database. Overall I find it easier to use because of it’s Object Oriented approach (although it can still be used progressively).

As always if you have any questions, comments or suggestions let me know, or you can find me on Twitter. A link to my profile is in the sidebar (the blue birds).


Author’s gravatar

how would i go about updating multiple tables in one single statement?

Author’s gravatar author

Hi Ric,

As far as I’m aware there is no specific command to insert data into multiple tables. You (I think) can however write two queries into one variable and hand them both to the $mysqli->query() function. For example:

The only problem with that is I think it will only return the result of the last query, so if you need to confirm that both updates worked you would have to run to separate queries.

Author’s gravatar

What about join table? same?

Author’s gravatar author


I’m not sure what you are referring to? Inserting into a join or retrieving records from a join?

Author’s gravatar

You have a typo there instead of $results = $mysqil it should be $mysqli 🙂 some goes for the other exemples.

Author’s gravatar author


I have this horrible habit of hitting keys in the wrong order sometimes, I normally catch it which proof reading but must have missed that. Thanks for letting me know, will fix it now. 😉

Author’s gravatar

Hi Paul,
You rock. Thanks for this detailed tutorial. However I feel that if you could give some entire php code example, it would be better for beginner like me 😉 . Moreover I would like to see about the way to implement transactions using mysqli If you have done it already, can you redirect me or to some other good ones. Again thank you so much.

I don’t know whether you reply to this or not because it’s very late reply from my side, but I found your tutorial just few days ago and I am glad that I found this.

Author’s gravatar author

Hi Bob,

Sadly I haven’t worked with transactions, it’s just something I haven’t needed to use in my work and so haven’t taken the time out to learn about them unfortunately.

Echoing out the results is pretty simple, not sure why I didn’t cover it in the tutorial.

If you need the code though let’s say you wanted to output the titles of the books from the last query on this tutorial. You could use:

The printf() is just how I’m doing it, but you could just as easily use echo() or print.

Author’s gravatar

Hi there,

if I am using an insert and obviously not what I’m getting from $ _POST as would make the sql statement?

$query = "INSERT INTO books SET title='%s', author='%s'";

I’m a bit new to this the MSQLI.
My English is not so good, hope you understand
Thx 🙂

Author’s gravatar author


I think I understand. I will do a third part to this tutorial covering a few more things, such as inserting data pulled from $_POST safely. Look out for it in the next day or so.

Older Comments
Newer Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I'll keep your WordPress site up-to-date and working to its best.

Find out more