Does the single row contains the value of ‘Count’? Can you try either accessing that value, or creating a query that returns the actual rows where id=’$item_number’?
Try this instead:
$rows = $wpdb->get_col( "SELECT COUNT(*) as num_rows FROM $table_name WHERE id='$item_number'" );
echo $rows[0] . ' Rows Found';
$check = $rows[0];
Or…
$wpdb->get_results( "SELECT * FROM $table_name WHERE id='$item_number' LIMIT 1" );
echo $wpdb->num_rows . 'Rows Found';
$check = $wpdb->num_rows;
That will select anything with that ID, and limit it so that if it finds one result it will only pass back that one. You can check that the $check value is 0 then.
I would suggest that it is somewhat more efficient to select only a single value rather than all the rows.
You’d think so, but not when you’re doing a COUNT() operation vs. the LIMIT 1 clause.
Using COUNT() you need to read every row in the table to get a complete count. Using my method with the LIMIT 1 you’ll only read every row if nothing exists. If a matching record does exist it will find that one, and then return it without reading the rest of the tables data rows.
So, how is $wpdb->num_rows set? It can’t be correct without reading all rows.
Mark_02660 is looking to see if one item is in the DB according to the part number that’s given so that there’s no duplicates. There’s no reason to look for more then one instance of this, as one is all that’s allowed, so you only ever need to look for one to see if it exists. If it does, don’t allow another one with the same ID, and if it doesn’t allow it to be added.
That may be, but how will $num_rows be correct without counting all rows, even if there is only one?
Never mind – no more debate.
It won’t be right if you want every row, but in this particular case, you don’t need every row. You only need to know if one already exists, not the exact number of how many exist, because in this case it should only ever be 0 or 1 so there’s no point in counting more then 1 record. That’s the big difference in the two approaches. Both are correct, it’s me being more of a pedantic programmer tring to optimise quries like this where possible.
Hi All,
Thanks, I did get it working the other day after finding that I had “get” options, get_var(), get_results() and so on.
This is what I came up with. And yes I know it does not echo properly, I was just testing.
$check = $wpdb->get_var( "SELECT COUNT(*) FROM $table_name WHERE id='$item_number'" );
echo "Item Number being checked: $item_number <br /> Found $check rown with that value in the db ";
if($check != 0)
{
// item number already exists
$error_msg.="<li>The item number is already in use</li>";
$item_number_css = 'formerror';
}
catacaustic,
I never though about putting a LIMIT 1 clause in before, good idea.
I apologize. Of course, you are correct @catacaustic. I forgot that we are talking about get_results() rather than query_posts() and confused num_posts with number_posts which is set by SQL_CALC_FOUND_ROWS and forces a read of the entire DB.
