Using PHP & MySQLi With A MySQL Database Part 2

April 29, 2010 / 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:

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "INSERT INTO books SET title='The Exorcist', author='William Peter Blatty'";
$results = $mysqli->query($query);

$mysqli = new mysqli('host', 'username', 'password', 'database');

$query = "INSERT INTO books SET title='The Exorcist', author='William Peter Blatty'";

$results = $mysqli->query($query);

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:

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "DELETE FROM books WHERE title='Interview With A Vampire'";
$results = $mysqli->query($query);

$mysqli = new mysqli('host', 'username', 'password', 'database');

$query = "DELETE FROM books WHERE title='Interview With A Vampire'";

$results = $mysqli->query($query);

Here is how to update data:

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince'";
$results = $mysqli->query($query);

$mysqli = new mysqli('host', 'username', 'password', 'database');

$query = "UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince'";

$results = $mysqli->query($query);

Simplez.

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.

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "INSERT INTO books SET title='The Exorcist', author='William Peter Blatty'";
$results = $mysqli->query($query);
printf("The query affected %s rows", $mysqli->affected_rows);

$mysqli = new mysqli('host', 'username', 'password', 'database');

$query = "INSERT INTO books SET title='The Exorcist', author='William Peter Blatty'";

$results = $mysqli->query($query);

printf("The query affected %s rows", $mysqli->affected_rows);

Miscellaneous Features

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

$mysqli = new mysqli('host', 'username', 'password', 'database');

if($mysqli->errno) {
    printf("Connection To Database Failed:<br/> %s", $mysqli->error());
    die();
}

$mysqli = new mysqli('host', 'username', 'password', 'database');

if($mysqli->errno) {

printf("Connection To Database Failed:<br/> %s", $mysqli->error());

die();

}

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

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = 'SELECT title FROM books';
$result = $mysqli->query($query);
printf("There is a total of %s Books", $result->num_rows);

$mysqli = new mysqli('host', 'username', 'password', 'database');

$query = 'SELECT title FROM books';

$result = $mysqli->query($query);

printf("There is a total of %s Books", $result->num_rows);

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

Basics Database MySQL MySQLi PHP

Paul Robinson

WordPress plugin & theme developer, user of Laravel and learner of React.js. Can usually be found listening to Japanese, and Korean Pop or Vocaloid music while developing.

12 Comments

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

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:

UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince';
UPDATE books SET author='Carl Sagan' WHERE title='Cosmos';

1 2	UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince'; UPDATE books SET author='Carl Sagan' WHERE title='Cosmos';

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.

What about join table? same?

author

Hi,

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

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince'";
$results = $mysqil->query($query);

$mysqli = new mysqli('host', 'username', 'password', 'database');

$query = "UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince'";

$results = $mysqil->query($query);

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

author

Hi,

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

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

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:

while ($row = $result->fetch_object()) {
    printf ("%s<br/>", $row->title);
}

while ($row = $result->fetch_object()) {

printf ("%s<br/>", $row->title);

}

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

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

Hi,

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

Using PHP & MySQLi With A MySQL Database Part 2

Inserting Data

Deleting Data

Determining The Affect

Miscellaneous Features

12 Comments

ric

Paul Robinson

bbmak0

Paul Robinson

Netra

Paul Robinson

bob

Paul Robinson

abr4xas

Paul Robinson

Leave a Reply Cancel reply

Categories

Recent Posts

Recent Comments

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

Inserting Data

Deleting Data

Determining The Affect

Miscellaneous Features

Follow Me

Categories

Recent Posts

Recent Comments

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