List:General Discussion« Previous MessageNext Message »
From:Reynier Perez Mira Date:February 7 2006 1:42am
Subject:Help with query optimization & query SUM
View as plain text  
I'm develop a simple shopping cart. I have this two tables:
IDU int(11) NOT NULL
IDA int(11) NOT NULL
CantidadDeArticulos int(11) NOT NULL

IDA int(11) NOT NULL auto_increment,
IDC int(11) NOT NULL default '0',
ANombre varchar(200) NOT NULL default '', ADescripcion text, ACantidad int(11) NOT NULL
default '0', AImagen varchar(50) default NULL, IDU int(11) NOT NULL default '0', APrecio
float(6,2) default NULL, KEY AI_IDA (`IDA`)

Before ask let me explain some things. As you can see in the tables I have the same field
IDU in both tables. So in first(table carro_de_compras) it means is user ID loged on
ecommerce system, the second is the user ID who upload articles for sale. Something like
eBay in wich you can sale and buy at every time. The arrive the point in wich I need to
optimize queries:

PHP Code:
$sql = mysql_query("SELECT * FROM carro_de_compras"); 
$sresultado = mysql_fetch_assoc($sql);

$query = mysql_query("SELECT * FROM os_articulo WHERE (IDA='".$sresultado['IDA']."')"); 
while ($record = mysql_fetch_assoc($query)) {  
 $productos[] = $record; 

The question for this problem is: exists any way to optimize this query and leave only in
one line? I read in MySQL doc about it and found some about JOIN but I can't understand
how it works. Maybe because I'm cuban and not understand english as well as I want.

The other questions is how to add some values to a field. For example:
$sql = mysql_query("UPDATE table SET value=value+1" WHERE id='1');

For do this query I do this:
$sql = mysql_query("SELECT value FROM table WHERE id='1'");
$result = mysql_query($sql);
$update = mysql_query("UPDATE table SET (value='".$result['value']."' + 1) WHERE id='1');

So is possible to optimize this query?

4to. año Ing. Informática
Usuario registrado de Linux: #310201
El programador superhéroe aprende de compartir sus conocimientos. 
Es el referente de sus compañeros. Todo el mundo va a preguntarle y él,
secretamente, lo fomenta porque es así como adquiere su legendaria
sabiduría: escuchando ayudando a los demás... 
Help with query optimization & query SUMReynier Perez Mira7 Feb
  • Re: Help with query optimization & query SUMUnknown Sender7 Feb