Hi,
always reply to the list please
2005/9/19, Miguel Cardenas <[EMAIL PROTECTED]>:
> > 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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
