How to check if a row exists in MySQL using PHP
I am trying to read in an XML file and compare it to fields in an existing database. If the ID in the database doesn’t exist in the XML file, then the whole row corresponding to the Id is no longer valid and will be deleted. To do this I read in each line of the XML from start to finish in a while statement. As step one I am trying to do a simple compare, and echo if it finds an Id in the database that doesn’t exist in the XML. I know there are some Ids in the database that don’t exist in the XML, but the following code is not displaying them. I’ve got three questions, firstly how would I display the Id that is pulled from the database, and secondly why isn’t this code finding any ids that are not in the XML? The final question is am I going about this completely the wrong way and is there a better way to do it!
$sql_result = mysql_query("SELECT id FROM `list` WHERE or die(mysql_error()); if($sql_result) < // echo $id . " Id exists " . $sql_result["id"] . "\n"; >else 6 Answers 6
Your code isn’t finding what you expect because even though the id may not be found, $sql_result still holds a TRUE value because the query was successful. Instead, check if myqsl_num_rows() > 0
if($mysql_num_rows($sql_result) > 0) < // echo $id . " Id exists "\n"; //Now, to print the id, you need to fetch it from `$sql_result`, //which is just a resource at this point: $row = mysql_fetch_assoc($sql_result); echo $row['id']; >This is a correct answer, but mind the one from @Gerep as well, it might be beneficial to get all id’s and do one single query using IN( id, id, id ) if there are a lot of entries.
как работает WHERE EXISTS в MySQL
Нашёл у себя в коде запрос с WHERE EXISTS и решил разобраться, как оно работает.
Втыкал-то я его когда-то копи-пастом, толком не разобравшись.
А тут решил выяснить. Я в последнее время все запросы для себя стараюсь прояснять.
Стал читать доку, но как-то не въехал. А параллельно профилировал все варианты получения того же самого.
Сам запрос такой
SELECT SQL_NO_CACHE n.id, title, date_pub FROM news n WHERE NOT EXISTS
(SELECT * FROM categories c WHERE c.category = 5 AND c.id=n.id )
ORDER BY n.date_pub DESC limit 10
включил профилировние
set profiling=1;
выполнил запрос, и запросил результат профайлинга, который выдал мне таблицу, которая многое проясняет:
show profile;
+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000077 |
| checking permissions | 0.000004 |
| checking permissions | 0.000003 |
| Opening tables | 0.000023 |
| System lock | 0.000009 |
| init | 0.000030 |
| optimizing | 0.000009 |
| statistics | 0.000094 |
| preparing | 0.000058 |
| executing | 0.000003 |
| Sorting result | 0.000007 |
| Sending data | 0.000030 |
| optimizing | 0.000010 |
| statistics | 0.000112 |
| preparing | 0.000013 |
| executing | 0.000002 |
| Sending data | 0.000042 |
| executing | 0.000002 |
| Sending data | 0.000028 |
| executing | 0.000002 |
| Sending data | 0.000032 |
| executing | 0.000003 |
| Sending data | 0.000041 |
| executing | 0.000002 |
| Sending data | 0.000020 |
| executing | 0.000002 |
| Sending data | 0.000020 |
| executing | 0.000002 |
| Sending data | 0.000027 |
| executing | 0.000002 |
| Sending data | 0.000021 |
| executing | 0.000002 |
| Sending data | 0.000019 |
| executing | 0.000002 |
| Sending data | 0.000020 |
| executing | 0.000002 |
| Sending data | 0.000024 |
| end | 0.000004 |
| query end | 0.000003 |
| closing tables | 0.000006 |
| freeing items | 0.000020 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+———————-+———-+
43 rows in set (0.00 sec)
Из хорошо видимых повторяющихся действий мы делаем предположение, что мускуль выполняет подзапрос для каждой найденной запросом строки, по результатам решая — добавлять или не добавлять её в итоговую выдачу.
причем подзапросы эти отрабатывают супербыстро, что видно из той же таблицы.
Предположение наше подкрепляется тем, что если в выдаче не встретилось категорий-исключений, то этих подзапросов выполняется 10 штук. Но если втречается такая категория, то количество подзапросов увеличивается: в приведённом примере их 11 — одна новость попала под фильтр, и вместо неё пришлось прочитать ещё одну.
То есть, это получается такой джойн для бедных, который, при этом, работает в 100 раз лучше запроса
WHERE id IN (SELECT id FROM table)
Но использовать его имеет смысл только с лимитированными запросами, поскольку если идёт большая выборка, то эти подзапросы, пусть даже и супербыстрые, в сумме всё равно дают ощутимое время выполнения.
Так что в итоге я всё равно отказался от WHERE EXISTS и сделал нормальный джойн,
SELECT * FROM news n LEFT JOIN categories c ON n.id = c.id and c.category = 5 WHERE c.id is null
который можно использовать как для получения самих новостей, так и их количества
(спасибо MiksIr с форума, который натыкал меня носом в идиотский тупняк с этим запросом)
Кстати, супербыстрота этих подзапросов объясняет эффективность handlerSocket-а: эти самые миллионные доли секунды из профайлинга — и есть реальное время обращения к storage engine, а остальное — это накладные расходы SQL-я.
Check if Record Exists
I found this on php.net to check if file exists. I want to check if record exists first, then do update, otherwise do an insert.
I know how to update or insert, but don’t know how to check if a record exists first. How is it done?
10 Answers 10
If you know how to do a SQL SELECT , then do that:
$result = mysql_query("SELECT * FROM table1 WHERE something"); $num_rows = mysql_num_rows($result); if ($num_rows > 0) < // do something >else < // do something else >Better yet, don’t do this in PHP, use INSERT . ON DUPLICATE KEY UPDATE .
You don’t need to do this in PHP, you can do it directly in the SQL query for your database (I’m assuming you’re using a database for the records, since that’s what it sounds like from your question, and I’ll assume MySQL since that’s often used along with PHP).
INSERT INTO . ON DUPLICATE KEY UPDATE; This would insert a new row if it doesn’t already exist, or update the current one if it does, assuming your tables have primary keys.
See the MySQL Manual for more info on this.
The other option is to just do a SELECT COUNT(1) FROM myTable WHERE . query first, and then only do an insert if the result is 0, otherwise do an update.
I would recommend Dominic Rodger’s solution, but with a little change to make it faster. You should select a single value and not more than one row.
$result = mysql_query("SELECT key FROM table1 WHERE something LIMIT 1"); $num_rows = mysql_num_rows($result); if ($num_rows > 0) < // do something >else < // do something else > If your record already exists, you’ll get a result, wich is more than 0 results, so it works, but potentially with less traffic from your SQL-Server.
INSERT . ON DUPLICATE KEY UPDATE $username = $_POST["user"]; $query = mysql_query("SELECT * FROM users WHERE username='$username'"); if(mysql_num_rows($query) != 0) < echo "Username already exists"; >else < //proceed with code here >Count records matching your criteria?
select count(*) from foo where > 0) < // record exists . >You could do a select for that record and inspect the result, or you could try an update and see if there was an error. If there was, then there was nothing to update, so do an insert.
You can set the specific columns in your database as primary key and then the insert will success only if you don’t have the record already. In this way, you don’t event need to check if record exists.
Strange no one has mentioned using REPLACE?
I’ve had the same problem and I solved using REPLACE INTO, a MySQL extension working this way:
- If the record which you want to insert does not exist, the MySQL REPLACE inserts a new record.
- If the record which you want to insert already exists, MySQL REPLACE deletes the old record first and then insert a new record.
I found this method useful when I don’t need to remember the old values of the record. For example, if you want to increase a value this method isn’t a great way ’cause delete the old record and the old values as well.
Remember also you need to have both INSERT and DELETE privileges to use REPLACE.
Here’s and example based on my code:
$conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); > $sql = "REPLACE INTO table_name (name, age, current_date) VALUES ('" . $name . "', $age, '" . date('Y-m-d') . "')"; if ($conn->query($sql) === FALSE) < echo "Error: " . $sql . "
" . $conn->error; > $conn->close(); How to Check if a Record Exists in a MySQL Database
Almost everyone has been in a circumstance when it is necessary to check whether a record exists in the MySQL database or not.
Let’s look through how to do that with the most suitable and easy command. Below you can find the right command and a common mistake that almost every beginner can make while trying to figure out the problem.
The Correct Command
If you want to check whether a record already exists in your database or not, you should run the following code:
// connect to database $mysqli = new mysqli("localhost","db_user","db_password","db_name"); $query = "SELECT * FROM products WHERE code = '$code'"; $result = $mysqli->query($query); if ($result) < if (mysqli_num_rows($result) > 0) < echo 'found!'; > else < echo 'not found'; > > else < echo 'Error: ' . mysqli_error(); > // close connection mysqli_close($mysqli); ?> $query = "SELECT * FROM products WHERE code = '$code'"; $result = mysql_query($query); if ($result) < if (mysql_num_rows($result) > 0) < echo 'found!'; > else < echo 'not found'; > > else < echo 'Error: ' . mysql_error(); > ?>A Common Mistake
Now, let’s check out a common mistake that we recommend you to overcome:
//Will NOT work $query = "SELECT * FROM products WHERE code = '$code'"; $result = mysql_query($query); if ($result) < echo 'found'; > else < echo 'not found'; > ?>What is MySQL
MySQL is considered an open-source relational database management system. It is aimed at organizing data into one or more data tables.
Several popular applications such as Twitter, Facebook YouTube, Google apply MySQL for data storage purposes. It was initially made for limited usage. Yet, now it is compatible with many essential computing platforms such as macOS, Linux Ubuntu, and Microsoft Windows.
check if SQL row exists with PHP
If you would like to use PDO (PHP Data Object) , then use the following code:
$dbh = new PDO("mysql:host=your_host_name;dbname=your_db_name", $user, $pass); $stmt = $dbh->prepare("SELECT username from my_table where username = ':name'"); $stmt->bindParam(":name", "bob"); $stmt->execute(); if($stmt->rowCount() > 0) < // row exists. do whatever you want to do. >Have in mind what the PHP documentation says about rowCount() : «If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.». So check first if your database returns the correct number or not.
Sayem’s answer has the most upvotes, but I believe it is incorrect regarding PDO.
For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100"; if ($res = $conn->query($sql)) < /* Check the number of rows that match the SELECT statement */ if ($res->fetchColumn() > 0) < /* Issue the real SELECT statement and work with the results */ $sql = "SELECT name FROM fruit WHERE calories >100"; foreach ($conn->query($sql) as $row) < print "Name: " . $row['NAME'] . "\n"; >> /* No rows matched -- do something else */ else < print "No rows matched the query."; >> $res = null; $conn = null;