How to check if a table is empty?

[maheswaran]

Hi Guys,

I want to check if a table is empty or not?

In my table (i.e test) have no records and id is a primary key as well as auto increment column.

i have using to find this "select count(id) from test"

It gives 1 .

ALso using others query but only get answer 1

how do find ?

[coolsti]

I think you posted your question in the wrong forum. It seems to be a database issue, not necessarilly a PHP issue. You may wish to post this elsewhere.

[Ferris]

I test it in mysql,it's ok...

select count(id) from test;

it will return 0
what database do you use?

[Atli]

Coolist is quite right, of course. This would not belong in the PHP forum, but rather in one of the database forums.
Considering that this was posted in the PHP forum, I have moved it over to the MySQL forum for now. Let me know if it should be somewhere else please.

As to your question, using the COUNT function should give you the results you need.
That is, if you were to do:

SELECT COUNT(*) FROM test;
 

And we assume the test table is empty, it should give you something like:

mysql> SELECT COUNT(*) FROM test;
+----------+
| COUNT(*) |
+----------+
|        0 | 
+----------+
 

To get that result in PHP, you would of course have to read the value returned by the query, as opposed to the row count.

[maheswaran]

Hi,

Yes my table is empty. I have using mysql 5.0.51 ....

But have result 1...

What i did wrong ????

[Atli]

If you did that, and you got 1, then there is 1 row in you table...

Try clearing out the table, like:

TRUNCATE test;
 

And then try again. See if the result changes.

[maheswaran]

ok

i have another problem now. I thing faukt is in my site.....

I have job table.. and job id is auto increment ... For job entry form i will show the job id in the column job id like below

Job id : 1 (from table job for this am using "select max(jid) from job" )

Job desc: <input box>

Add button

while doing mutiple adding there is no probs ...job id increasing and i show the next job id in form..
but while am did bul delete... There is no record in table.....now job id comes again 1.... not next one.... can any one give some logical idea....

[r035198x]

..
but while am did bul delete... There is no record in table.....now job id comes again 1.... not next one.... can any one give some logical idea....

Could you explain yourself again and post some relevant code?

[maheswaran]

Here My Code

//Generate Job No
$qry="select max(jid) from jobd";
$res=mysql_query($qry);
$cnt=mysql_num_rows($res);
list($jid)=mysql_fetch_array($res);
 

<table cellspacing="5" cellpadding="1" border="0" align="center" width="80%">
<td>
<tr><th colspan="2"><br>Job Entry Form</th></tr>
<tr><td>Job No</td><td><input type="text" tabindex="1" name="jobid" value="<? echo $jid;?>" class="normal" readonly></td></tr>
<tr><td>Job Description</td><td><!--input type="text" tabindex="1" name="des" value="<? echo $jdescription;?>"--><textarea name="des"><? echo $jdescription;?></textarea></td></tr>
<tr><td>Client Matter #</td><td><input type="text" tabindex="1" name="cmatter" value="<? echo $cmatter;?>"></td></tr>
<tr><td>Attachment</td><td><input type="file" tabindex="1" id="attachment" name="attachment"></td></tr>
<tr><td></td><td align="left"><input type="hidden" name="pageaction"  value="<?=$action;?>"><input type="submit" name="submit" value="submit">
</table>
 

Here am displayinh job id in entry form and asking user to put related data like description and attachment.....

While every sucessful addi job id increasing . if am added 5 records then am displaying job id 6 in form to be entered ...

Every thing is ok until this...

If am delete all files in table then job id become 1 instead of 6 (before that added 5 records and all reocrds were deleted, now id should come 6.... not one 1)'

[r035198x]

Are you using the value returned from

$qry="select max(jid) from jobd";

for your next ID? you shouldn't be doing that if you set the column to auto increment. After you delete everything from the table that value will be zero and if you use it's next value then 1 will be saved which is not what you want.

[maheswaran]

k fine.then can you tell me any idea regd this.....to increse my jobid

[r035198x]

k fine.then can you tell me any idea regd this.....to increse my jobid

If you set the columns to auto increment then it will increase automatically.

[maheswaran]

k i did this trick, i have mainting a jobid into another table called temp for every auto increment or deletion there is no effect in this table only in jobid table