List:General Discussion« Previous MessageNext Message »
From:Pooly Date:September 19 2005 8:04am
Subject:Re: mysql_free_result() WITHOUT mysql_fetch_row()
View as plain text  
Hi,

always reply to the list please

2005/9/19, Miguel Cardenas <renito73@stripped>:
> > I'm a bit confused. If you just need to know if there is data matching
> > a criteria, a count(*) is enough and will do absolutely the same thing
> > that you want, and spare you the mysql_store_result with a whole
> > dataset.
> > count(*) as nothing to do with knowing is there is data in the table or
> > not... If I'm still wrong, could you provide a sample query ?
> 
> Well maybe am making a storm in a glass trying that and possibly there is
> another solution...
> 
> There is a table like this:
> 
> --------------------------------------
> |   id INT   |   list CHAR(16)   |
> --------------------------------------
> 
> I have (id,list) pairs, there may be duplicate id's or list's separately, but
> together may exist unique pairs.
> 
> a,1 <--- ok
> a,2 <--- ok
> b,1 <--- ok
> b,2 <--- ok
> a,1 <--- error, duplicated pair
> 
> so, in pseudocode I do this
> 
> lets supose I want to insert (x,something)
> 
> while ( retrieve data from file ) {
>    1. select id,list from mytable where id=x and list=something
>    2. use_result()
>    3. fetch_row()!=NULL ? (data exists already)
>       yes: don't do nothing
>       no:  insert into mytable values(x,something)
>    ...
> }
> 
> the process is repeated thousands of times, so, retrieving all data in every
> loop would make a big difference, 'cos that I use use_result() instead of
> store_result() thas would retrive all data in every loop...


Have you try :
select count(*) from mytable where id=x and list=something
if count ==0, it's like fetchrow==NULL from your solution, but without
all the fuss about use_result();

> 
> I am not very experienced in mysql, with a 'UNIQUE' option for a field allows
> only one unique field, but in this case fields may be duplicated, what can
> not be duplicated are pairs, 'cos that first I see if it already exists on
> the table before insertion.

You may run into problem if two process access your table at the same time.
First solution would be to : lock the table, check the existance,
insert if it's ok, unlock the table.
Second solution :
ALTER TABLE mytable ADD UNIQUE(id,list)
which make a unique index on two field. inserting a duplicate value
would give you back an error and let the table untouched.

> 
> Maybe you have a suggestion to do the same in another way.
> 
> Thanks
> 


-- 
Pooly
Webzine Rock : http://www.w-fenec.org/
Thread
mysql_free_result() WITHOUT mysql_fetch_row()Miguel Cardenas17 Sep
  • Re: mysql_free_result() WITHOUT mysql_fetch_row()Dan Nelson17 Sep
    • Re: mysql_free_result() WITHOUT mysql_fetch_row()Miguel Cardenas18 Sep
      • Re: mysql_free_result() WITHOUT mysql_fetch_row()Pooly18 Sep
        • Re: mysql_free_result() WITHOUT mysql_fetch_row()Miguel Cardenas18 Sep
          • Re: mysql_free_result() WITHOUT mysql_fetch_row()Pooly19 Sep
Re: mysql_free_result() WITHOUT mysql_fetch_row()Pooly19 Sep
  • Re: mysql_free_result() WITHOUT mysql_fetch_row()Miguel Cardenas19 Sep
    • Re: mysql_free_result() WITHOUT mysql_fetch_row()SGreen19 Sep