Check if value exist in mysql

Question

I have a small script to upload data to mysql database from a csv file and I want to check the list of values that are inside of the csv file.

CSV File:

code,alert_quantity
12345,10

PHP File:

    <?php
    $link_id = mysql_connect("localhost", "root", "")
               or die("Could not connect.");

    if(!mysql_select_db("database",$link_id))
    die("database was not selected.");

    function _checkIfCodeExists($code){

    $sql        = "SELECT COUNT(*) AS count_no FROM products WHERE code = ?";
    $count      = $sql;
    if($count > 0){
        return true;
    }else{
        return false;
    }
    }

    function _updateData($line_of_data){
    $code            = $line_of_data[0];
    $newAlert       = $line_of_data[1];

    $sql = "UPDATE ";
}

    $file_handle = fopen("file.csv", "r");

    while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE) {

    $query = mysql_query("SELECT * FROM products WHERE code ='$line_of_data[0]'") or die(mysql_error());
    $message = '';
    $count   = 1;

    if(_checkIfCodeExists($line_of_data[0])){
        try{
            _updateData($_data);
            $message .= $count . '> Success:: Product with code  (' . $line_of_data[1] . ') Exist (' . $line_of_data[0] . '). <br />';

        }catch(Exception $e){
            $message .=  $count .'> Error:: While updating alert (' . $line_of_data[1] . ') of code (' . $line_of_data[0] . ') => '.$e->getMessage().'<br />';
        }
    }else{
        $message .=  $count .'> Error:: Product code   (' . $line_of_data[0] . ')   Doesnt exist<br />';
    }
    $count++;
}
echo $message;

?>

I don't want to import this values, just I want to check if exist and get the result.

Any help is appreciated.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Madara's Ghost, Jan 15 '13 at 17:52
Please, stop using `mysql_query`. You're going to get yourself in serious trouble, especially with this casual attitude towards not escaping your data properly. — tadman, Jan 15 '13 at 17:52
Please note that mysql_* are deprecated. Use mysqli or PDO::query() instead. Use for you notice. — EirikO, Jan 15 '13 at 17:52
Why don't you create a `SELECT ... LIMIT 1` and check it's result ? — Ricardo Alvaro Lohmann, Jan 15 '13 at 17:53
What do you want to do ? Because if you want to insert them if they are not present you should probably look at unique keys and set them in your BDD (i suggest you to have a look at http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — NeeL, Jan 15 '13 at 17:57
Hello, i just updated the script but im getting the result that product doesn't exist. — Dar, Jan 15 '13 at 19:49

Kyle · Answer 1 · 2013-01-15T18:29:38.153

1

As others have said, you should really use PDO. But, to answer your question, mysql_num_rows is your solution.

$query = mysql_query("SELECT * FROM products WHERE code='$line_of_data[0]' AND alert_quantity = '$line_of_data[1]'") or die(mysql_error());

if (mysql_num_rows($query))
// Data is already in database
else
// Data not in database

edited Jan 15 '13 at 18:29

answered Jan 15 '13 at 17:55

Kyle

1,727
1
12
22

Don't forget to check `$query` before checking `mysql_num_rows($query)` – Madbreaks Jan 15 '13 at 17:57
There is no need for that, if the $query is bad then mysql_num_rows will return false. – Kyle Jan 15 '13 at 18:18
...and you will get a Notice (if not a warning) stating that `$query` is not a valid resource. Beyond that, your code suggests that if the `mysql_num_rows` call returns false, the data is already in the database. But what if the query failed? You're not doing sufficient error handling. – Madbreaks Jan 15 '13 at 18:24
I wasn't writing the script for him, just giving him an example of how to do what he wants. I added a die so if the query is bad it will catch it. – Kyle Jan 15 '13 at 18:30
If your answer is purposefully incomplete, it's good to call that out in your answer. – Madbreaks Jan 15 '13 at 18:31
What i want to do is to echo code 12345 exist, code 12346 doesn't exist. – Dar Jan 15 '13 at 18:44
...which is exactly what this will do. – Kyle Jan 15 '13 at 18:56
this will check if the data from csv file exist in table column selected. – Dar Jan 15 '13 at 19:09
Yes, which is what you asked to do. – Kyle Jan 15 '13 at 19:11
Hello, i just updated the script but im getting the result that product doesn't exist. – Dar Jan 15 '13 at 19:50
You must of done SELECT * FROM product instead of SELECT * FROM products – Kyle Jan 15 '13 at 19:59

score 0 · Answer 2 · edited Dec 19 '13 at 13:04

0

Please check mysql exists subqueries.

Exists and Not Exists subqueries

edited Dec 19 '13 at 13:04

NANNAV

4,718
4
26
48

answered Jan 15 '13 at 18:32

Ayaz

274
1
3
14

Hello, i just updated the script but im getting the result that product doesn't exist – Dar Jan 15 '13 at 19:53
A link is not much of an answer, IMO. – Buttle Butkus May 21 '13 at 18:35

Check if value exist in mysql

2 Answers2

Linked

Related