Trying to compare from DB in php! (Pdo) help appreciated

Development Code
1

I've been trying to take information from my database, then comparing it to a set variables that I declared.
(dbConnect() is just a function connecting to the database.)
I.e.

<?php
include_once('connection.php');

$data = 'string';
$data_two = 'another string'

$checkCom = dbConnect()->prepare("SELECT row_one, row_two FROM address WHERE row_one == '$data' AND row_two == '$data_two'");

if (!checkCom) {
die('Query failed to execute for some reason');
}

if (dbConnect()->query($checkCom) > 0) {
echo "Data already exists";
} else {
echo "all good";
}
?>

The issue is I can't get it to compare. Keep getting errors. "query() expects parameter 1 to be string" was the last known error.

2

That select statement isn't a comparison and you also don't select from Rows, you select from Columns.

If you have a customer table with CustID, FirstName, LastName, and EmailAddress columns the Select statement would be;

SELECT FirstName, LastName FROM Customers WHERE FirstName = 'Bob' AND LastName = 'Smith'

which would give you the dataset of all your customers called Bob Smith.

I couldn't run;

SELECT 'Bob', 'Smith' FROM Customers WHERE 'Bob' = 'Bob' AND 'Smith' = 'Smith'

Which is what your query looks like.

If I thought I might have two customer ID's for the same customer (this is really common on ecommerce sites where logic was not created in the application or database to prevent it) I would need to do something that searches on particular parameter values to help narrow the dataset;

SELECT CustID From Customers WHERE LastName = $Data and EmailAddress = $data_two

This would give me a dataset for all the rows which contain the same surname and email address. This could be used as a pre-check when someone tries to sign up a new account; if their email address is already in the table you return your "Data already exists" string.

I hope that helps, I often use http://sqlfiddle.com to work out SQL query syntax etc.

3

Without knowing the full context I'd probably go for BGL's answer because you then have the pk of that data entry, in case you need to reference either of the tables again.

Alternatively, you could go for a simple count.

var1: select count(*) from sql_table where FirstName = 'Foo' and LastName = 'Bar'
var2: select count(*) from sql_table2 where FirstName = 'Foo' and LastName = 'Bar'
If (var1 > 0) and (var2 > 0) { \\ Data exists in both tables }
4

Ah yeah, I got column and row mixed up there. :)

I'm trying to make like you said, kinda like a pre-check more like. Was trying to turn from sql to pdo. So how would I check for the existing data in the database, to what's trying to be entered? (Kinda new to PDO.)
SQL:

$data = 'string';
$data_two = 'string two';

$query = mysql_query("SELECT column_one, column_two FROM table WHERE column_one = $data AND column_two = $data_two ")

if (mysql_num_rows($query) > 0) {
echo 'Data already exist.'; // checks for existing data
} else {
echo 'Data is added.'; // adds data
}

5

@FireBall571, this is very bad:

SELECT CustID From Customers WHERE LastName = $Data and EmailAddress = $data_two

From your description above, sounds like $Data and $data_two come from user input. You must never put raw user input into your query. Best case, you will have occasional sql errors; worst case, someone will pwn your database and quite possibly your entire server. Learn more about SQL Injection.

Use a parameterized query instead:

<?php
// prepare the query with named parameters
$stmt = $PDO->prepare("SELECT CustID FROM Customers WHERE Lastname = :last AND EmailAddress = :email");

// execute the statement with user input
$stmt->execute( ["last"=>$Data,"email"=>$data_two] );

In this way, the user input is kept completely separate from the SQL commands, so there is no chance of SQL Injection. Learn more about prepared statements in PDO.

6

What if I told you they were not user input. :) But I might use what you described for future projects. Thanks!

7

@fireball571 that's a good point made by @traq2, if you are coding (especially for a front end system) ensuring you are doing it in such a way to prevent SQL injection is a must.

I've never really worked with MySQL/PDO but I seem to remember something about setting the SQL mode to NO_BACKSLASH_ESCAPES as well to prevent SQL injection. I expect @traq2 can confirm if that's still required or good advice.

8

Ah never mind. I typed out new code with mysqli instead of pdo and got desired results. :)

9

Everything I mentioned above applies to MySQLi, just the same. You need to use parameterized queries, and you must never put user input directly into your SQL statements.

@BGL, using prepared statements, escaping is completely unnecessary. The data travels to the DB separately from the statement, so there is zero opportunity for them to be confused.

Well, this implies they are user input... pretty strongly:

But even if it's not user input, parameterized queries have a performance edge. Plus it's just a really good habit to get into.

1 Like
10

Alright I will definitely give it a try, thanks for the suggestion!