2 queries or 1 in mysqli using php?

Question

I have a booking system with two tables. One table for the users (called users) so that the user know if it has booked it or not and another table for the teacher (called bookings) so that the teacher knows which ones has signed up for their class. Below is an example of when a user unbooks a class.

$new_classes_booked=$classes_booked-1;
$sql2 = $con->query("UPDATE users SET $classes_booked='{$new_classes_booked}' where userID='{$User}'");

$sql2 = $con->query("DELETE FROM bookings where user='{$user}' AND id_class='{$id_classs}'");

This system will be used by many users and teachers simultaneously. Given that, is there a more efficient and more secure way to execute this?

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 05 '16 at 13:29
Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 05 '16 at 13:29
`This system will be used by many users and teachers simultaneously` How many thousands of requests per second are you talking about? If it's less than 1000/second, then I'd worry more about the security of your queries than premature optimisations at this stage — Mark Baker, May 05 '16 at 13:31

score 0 · Answer 1 · answered May 05 '16 at 13:39

0

I would suggest you to execute two queries, one after another. Second one will execute so fast that it will not make any difference. How ever you can take a look at mysqli_multi_query() function.

answered May 05 '16 at 13:39

Parth Panchal

131
8

Your Common Sense · Answer 2 · 2016-05-05T14:04:09.713

Is there a more efficient way to execute this?

Yes.
To be frank, the table structure is just awful, violating every single rule of database design.

There should be nothing like $classes_booked in the users table. The number of booked classes should be taken from the bookings table.

Is there a more efficient and more secure way to execute this?

Yes.
Use prepared statements.

As my mysqli tutorial is not ready yet, here is PDO-based secure query:

$sql = "DELETE FROM bookings where user=? AND id_class=?";
$con->query($sql)->execute([$user,$id_class]);

2 queries or 1 in mysqli using php?

There is absolutely nothing wrong with running 2 or more queries in PHP. A typical application is running several dozens SQL queries on every hit, and feels perfectly healthy.

As long as you are not running queries in a loop, use whatever number of queries you find appropriate for your task.

Thank you! I am curious and new to sql and php security. So is there a simple way to explain withing this example why this PDO-based secure query is more secure than my initial way of writing the query? — user5095266, May 05 '16 at 14:17

2 queries or 1 in mysqli using php?

2 Answers2