StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat's the best way to test if a value exists in an sqlite table?
text
Body
copied!<p>First question here, so please don't whack me if i do something dumb!</p> <p>I'm creating a login system including registration etc. and with the registration i need to test 3 things to see if they are already in use.</p> <p>My initial test code looks something like this:</p> <pre><code>$IDq = $userdbc->query("SELECT * FROM users WHERE userID = '$uID'"); $IDq->setFetchMode(PDO::FETCH_ASSOC); $IDf = $IDq->fetch(); if($IDf['userID']){ echo '<p>ID exists</p>'; } else{ echo '<p>ID does not exist?</p>'; } </code></pre> <p>I'm going to need to test for possible duplicate email and display names.. i'm sure there must be a better way to go about this?</p> <p>Or am i really going to have to repeat the process 3 times to test for each item?</p> <p>This is my first real venture using a database solution.. Any usefull input is very much appreciated - thanks in advance!</p> <p>EDIT: I do need to test each value individually so that i can rebuild the registration form with appropriate error message's stating exactly what needs to be changed. Sorry i should have been more specific.</p> <hr> <p>EDIT: <strong>The Solution</strong> - thankyou Shakti Singh, and everyone else for the help.</p> <p>The function (using prepared statement with named placeholder):</p> <pre><code>function item_exists($dbc, $col, $val){ $query = $dbc->prepare("SELECT * FROM users WHERE $col = :val"); $query->execute(array(':val' => $val)); $fetch = $query->fetch(); if($fetch[$col]){ return true; } else{ return false; } } </code></pre> <p>How i'm using it within my registration validation function:</p> <pre><code>$userdbc = new PDO('sqlite:db/users.s3'); if(item_exists($userdbc, 'userID', $_POST['id'])){ $valid = false; array_push($sub['e'], 'User ID is not available.'); } if(item_exists($userdbc, 'userEmail', $_POST['email'])){ $valid = false; array_push($sub['e'], 'Email Address is already in use.'); } if(item_exists($userdbc, 'userName', $_POST['name'])){ $valid = false; array_push($sub['e'], 'Display Name already taken.'); } $userdbc = null; </code></pre> <hr> <p>EDIT: <strong>Additional:</strong></p> <p>As i am only testing one item, within the <code>item_exists()</code> function i substituted:</p> <pre><code>$query->execute(array(':val' => $val)); </code></pre> <p>for:</p> <pre><code>$query->bindParam(':val', $val); $query->execute(); </code></pre> <p>..just because executing an array seemed kinda dumb to me as it only holds one key/value..</p> <p>No idea what the actual difference might be, but hey its another option.</p> <p>EDIT: <strong>Additional-Additional:</strong></p> <p>The function with all changes made:</p> <pre><code>function item_exists($dbc, $col, $val, $rn){ // updated the prepare statement as per James Anderson's suggestion. $query = $dbc->prepare("SELECT COUNT($col) AS dupli FROM users WHERE $col = :val"); $query->bindParam(':val', $val); $query->execute(); $fetch = $query->fetch(); // $fetch['dupli'] contains the number of matches found. // $fetch[0] should also work, but not tested. if($rn){ // if $rn = true, return the number of finds return $fetch['dupli']; } else{ // else we are just testing for a match so.. if($fetch['dupli'] > 0){ // if we have one return true; } else{ return false; } } } </code></pre> <p>I just felt i should add this in case anyone else struggled like i did, always nice to have working examples - with comments too ^_~</p> <p>I'm fairly sure this will be my last edit but meh.. never know.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload