Retrieving 1 result from DB

[NathanLedet]

I'm using this simple script to pull multiple results from a database:

$query = "SELECT * FROM table";
$result = mysql_query($query) or die('Error selecting');
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
   //code here for each result
}

 

How would I modify that code to pull out only one result instead of an array with multiple results?

[akitchin]

well first off, it depends on what you're after.  if you just want to restrict it to the first record you get from the query, you can either specify it in the query itself:

 

SELECT * FROM table LIMIT 1

 

or you can just grab the first record without looping:

 

$row = mysql_fetch_array($result4, MYSQL_ASSOC);

 

if you want to specify which row to grab, look into the WHERE clause.  the PHP and MySQL manuals can give you a bit more information on this.

[NathanLedet]

Thanks. Also, I screwed up that code a bit... it should be $result, not $result4...I fixed it in my first post...just for clarification

 

beyond that... What I'm doing is comparing to make sure it doesn't already exist.

 

$testVar = "test";

$query = "SELECT * FROM table";

$result = mysql_query($query) or die('Error selecting');

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

  //code here for each result

}

 

How would I check to see if "test" already exists inside the field?  I couldn't say SELECT * FROM table WHERE field = "test", because what if it didn't exist?

[akitchin]

if it didn't exist, then it won't find any rows.  remember that "0" means just as much as "1".  since all you're doing is trying to find out if it exists, just use a COUNT() function instead:

 

SELECT COUNT(*) FROM table WHERE field='test'

 

to grab the value, use:

 

$value_found = mysql_result($result, 0, 0);

 

if value is 0, it doesn't exist in the table yet.  if it's 1 (or higher), then it's already in there.  using an aggregate function like COUNT() is far faster and more efficient on the database than forcing it to pull that record, especially since you won't be using the record anyway.  you just want to know if it's there.