php sqlite check if row exists

Question

I am trying to see if there is an existing row in my sqlite database and if not add it. But something is wrong with my query. I hope someone can spot my error.

Specifically I thin the error is in the query line:

$query = 'SELECT * FROM plant WHERE common_Name = '. $commonName . ' AND latin_Name = '. $latinName . 'AND  url = '. $URL ;

Thank you, Todd

// set path of database file
$file = "db/plants.db";

// create database object
$db = new SQLiteDatabase($file) or die("Could not open database");

// see if the EXACT same tag exists
$query = 'SELECT * FROM plant WHERE common_Name = '. $commonName . ' AND latin_Name = '. $latinName . 'AND  url = '. $URL ;
$result = $db->query($query) or die("Error in query");
$rows = sqlite_num_rows($result);


if($rows>0) return; //do not add it

// generate query string
$query = 'INSERT INTO plant (common_Name, latin_Name, url) VALUES("'.$commonName.'","'. $latinName.'","'.$URL.'")';

// execute query
// return result object
$result = $db->query($query) or die("Error in query");


// destroy database object
unset($db);

This should answer your question: stackoverflow.com/questions/531035/… — German Rumm
– German Rumm, Commented Jun 17, 2012 at 14:12
Have you tried object-oriented style? i.e. $rows = $result->numRows(); Not sure if it would make a difference... — Zencode.dk
– Zencode.dk, Commented Jun 17, 2012 at 14:25
@Inner-Design: I am new to sqllite and am slowly learning the OOP sqlite api. — maddogandnoriko
– maddogandnoriko, Commented Jun 17, 2012 at 14:38

Marc B · Accepted Answer · 2012-06-17 14:36:21Z

3

You're missing quotes in your SELECT query, so it's failing.

$query = "SELECT * FROM plant WHERE common_Name = '$commonName' AND latin_Name = '$latinName' AND  url='$URL'";
         ^                                        ^           ^ etc...

Note the indicated quote changes and formatting differences. As well, unless you've done it somewhere else outside of your code snippet, be aware that this type of query construction is vulnerable to SQL injection attacks, and you should read up and learn about those before you go any farther with this code.

Next time you put in an error handler (kudos for checking the return value, by the way), don't just output a static "Something went wrong" error message. That's useless for diagnostics. The database can tell you exactly what went wrong, so output the DB's error message instead, e.g. sqlite_error_message().

answered Jun 17, 2012 at 14:36

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

maddogandnoriko Over a year ago

did you mean sqlite_error_string? I could not find any docs on sqlite_error_message.

maddogandnoriko · Accepted Answer · 2012-06-17 14:36:47Z

0

I hate to answer my own question, but....

I was missing the double quotes around the parameters.Here is a query that works.

$query = 'SELECT * FROM plant WHERE common_Name ="'. $commonName .'" AND latin_Name = "'.$latinName .'" AND url = "'.$URL.'"';

Todd

answered Jun 17, 2012 at 14:36

maddogandnoriko

1,0022 gold badges14 silver badges31 bronze badges

Collectives™ on Stack Overflow

php sqlite check if row exists

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related