check query row count with php

Question

I am very green in PHP. I need to create a small service on server side which will grab data, transform it and populate it into MySQL database.

Everything is fine so far. I managed to create PHP script which updates existing rows in my database.

I have difficulties with checking If I need to update or add rows. Could you help me?

$servername = "localhost";
$username = "almazini";
$password = "3334444";
$dbname = "almazini";

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

then I have a loop through my array ... and executing SQL query:

$sql = "UPDATE MyTargetTable SET SRate = $sr, BRate = $br WHERE Name='$name' AND SName = '$surname' ";
$stmt = $conn->prepare($sql);
$stmt->execute();

How can I check if there is a row in MyTargetTable which I should update? If there is no such row - I would like to fire another query to insert new row.

hjpotter92 · Accepted Answer · 2015-12-22 07:12:17Z

2

You can set a UNIQUE constraint on the MySQL table itself to not allow duplicate values. Run the following from your mysql connection console:

ALTER TABLE MyTargetTable
ADD UNIQUE INDEX FullName (Name, SName);

Next, use the INSERT ... ON DUPLICATE KEY UPDATE query:

$sql = "INSERT INTO MyTargetTable VALUES (what, ever, values, here)
ON DUPLICATE KEY UPDATE SRate = :sr, BRate = :br";

and then bind the variables $sr and $br later as shown in examples here.

answered Dec 22, 2015 at 7:12

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Almazini Over a year ago

Very elegant solution which does exactly what I need! thanks a lot!

Muhammad Nouman · Accepted Answer · 2015-12-22 07:05:11Z

0

You should first check from database by passing your parameters in the query and if the count returned is greater than 0 than update, otherwise insert the new row.

Algorithm is like this

$count= "SELECT count(*) as count FROM MyTargetTable WHERE Name='$name' AND SName = '$surname'" ;

if(count > 0)
$sql = // Your Update Query
else
$sql = //your insert query

Hope this helps

answered Dec 22, 2015 at 7:05

Muhammad Nouman

616 bronze badges

Collectives™ on Stack Overflow

check query row count with php

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related