Hi everyone,
I have a strange problem. When I execute a query on mysql console it gives me 30 rows. But if I execute the same query on php I got 60 records (verified by mysql_num_rows). I had about 20 queries and only that specific one has that problem. If there is a known problem or a common error please let me know.
Here are my php code, html output and mysql output. I include db information at the end, but did not include any db data.
Thanks all in advance.
Tolga "thorr" Orhon
PS: I am submitting this problem for the second time as a new thread to put in compact form. Sorry for any inconvenience I may cause.
<BODY>
<TABLE>
<TR>
<TD WIDTH=50>ID</TD>
<TD WIDTH=200>PRODUCT</TD>
<TD WIDTH=75>PRICE</TD>
<TD WIDTH=50>CAPACITY</TD>
</TR>
<?
//Server Connection
if(!($dbLink = mysql_pconnect("localhost","root","tfa123././")))
{
print("Failed to connect to database!<BR>\n");
exit();
}
//select database
if(!mysql_select_db("thorr_test",$dbLink))
{
print("Can't use database!<BR>\n");
exit();
}
$qryIdeHdd = "SELECT i.item_id, i.name, i.price, sd.spec+0.0 as capacity ";
$qryIdeHdd .= "FROM item i, spec_def sd, spec s1, spec s2, cat c ";
$qryIdeHdd .= "WHERE i.item_id = s1.item_id ";
$qryIdeHdd .= " AND s1.item_id = s2.item_id ";
$qrtIdeHdd .= " AND s2.desc_id = 26 ";
$qryIdeHdd .= " AND s1.desc_id = 23 ";
$qryIdeHdd .= " AND i.cat_id = c.cat_id ";
$qryIdeHdd .= " AND c.maincat_id = 5 ";
$qryIdeHdd .= " AND s2.spec_id = sd.spec_id ";
$qryIdeHdd .= " AND s1.spec_id = 72 ";
$qryIdeHdd .= "ORDER BY capacity, name;";
if(!($recIdeHdd=mysql_query($qryIdeHdd, $dbLink)))
{
print("Query error!...<BR>\n");
print("MySQL say : " . mysql_error() . "<BR>\n");
print("Query was : $qryIdeHdd<BR>\n");
exit();
}
$intRecNum = mysql_num_rows($recIdeHdd);
while($rowIdeHdd = mysql_fetch_object($recIdeHdd))
{
print("<TR>\n<TD>$rowIdeHdd->item_id</TD>\n<TD>$rowIdeHdd->name</TD>\n");
print("<TD>$$rowIdeHdd->price</TD>\n<TD>$rowIdeHdd->capacity</TD>\n</TR>\n");
}
print("<TR><TD COLSPAN=4>Total Records: $intRecNum </TD></TR>\n");
?>
</TABLE>
</BODY>
ID PRODUCT PRICE CAPACITY
229 10 GB 5400 rpm 9.5ms Ultra ATA66 (MX91021/80/24 by Maxtor) $111.60 0.0
198 10 GB 5400 rpm 9.5ms Ultra ATA66 conner $85.20 0.0
230 10.2 GB 9ms 7200 rpm Ultra ATA66 ( mx51024/91024u3 BY Maxtor) $108.00 0.0
201 10.2 GB 9ms 7200 rpm Ultra ATA66 by Fujitsu $104.40 0.0
218 13GB 5400 rpm (AC313000A by WD) $104.40 0.0
236 13GB 7200 rpm (WD136BA by WD) $120.00 0.0
216 15 GB 9.5ms 5400 rpm Ultra ATA66 $117.60 0.0
221 15 GB 9.5ms 5400 rpm Ultra ATA66 (WD153AA by WD) $108.00 0.0
217 15 GB 9ms 7200 rpm Ultra ATA66 by Maxtor $117.60 0.0
207 15 GB 9ms 7200 rpm Ultra ATA66 WD $126.00 0.0
226 15GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307015 by IBM) $151.20 0.0
220 17GB 5400 rpm (MPD4173AT by fujitsu) $128.40 0.0
214 20.5 GB 9.5ms 5400 rpm Ultra ATA66 by WD $126.00 0.0
222 20.5 GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307020 by IBM) $150.00 0.0
199 20.5 GB 9ms 7200 rpm Ultra ATA66 (AC205BA by WD) $141.60 0.0
210 30 GB 5400 RPM 9.5ms $141.60 0.0
211 30 GB 7200 RPM 9ms ( MX54098U6 by Maxtor) $168.00 0.0
224 30 GB 7200 RPM 9ms Ultra ATA66 2M cache ( DTLA307030 by IBM) $174.00 0.0
208 4.3 GB 5400 RPM 9.5ms WD $84.00 0.0
204 4.3GB 5400 rpm Fujitsu $81.60 0.0
202 40 GB 9ms 7200 rpm Ultra ATA66 $217.20 0.0
227 40GB 9ms 7200 rpm Ultra ATA66 (MX54098U8 by Maxtor) $217.20 0.0
215 45GB 5400 RPM 9.5ms (WD450AA by WD) $168.00 0.0
223 45GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307045 by IBM) $228.00 0.0
205 6.4GB 5400 rpm (AC36400A/26400A by WD) $103.20 0.0
219 6.4GB 5400 rpm (MPB/MPC by fujitsu) $84.00 0.0
225 60GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307060 by IBM) $414.00 0.0
228 61GB 9.5ms 7200rpm Ultra ATA66 (MX96147U8 by Maxtor) $258.00 0.0
209 8.4 GB 5400 RPM 9.5ms WD $91.20 0.0
203 8.4GB 5400 rpm (MPBMPC by Fujitsu) $94.80 0.0
208 4.3 GB 5400 RPM 9.5ms WD $84.00 4.3
204 4.3GB 5400 rpm Fujitsu $81.60 4.3
205 6.4GB 5400 rpm (AC36400A/26400A by WD) $103.20 6.4
219 6.4GB 5400 rpm (MPB/MPC by fujitsu) $84.00 6.4
209 8.4 GB 5400 RPM 9.5ms WD $91.20 8.4
203 8.4GB 5400 rpm (MPBMPC by Fujitsu) $94.80 8.4
229 10 GB 5400 rpm 9.5ms Ultra ATA66 (MX91021/80/24 by Maxtor) $111.60 10.0
198 10 GB 5400 rpm 9.5ms Ultra ATA66 conner $85.20 10.0
230 10.2 GB 9ms 7200 rpm Ultra ATA66 ( mx51024/91024u3 BY Maxtor) $108.00 10.2
201 10.2 GB 9ms 7200 rpm Ultra ATA66 by Fujitsu $104.40 10.2
218 13GB 5400 rpm (AC313000A by WD) $104.40 13.0
236 13GB 7200 rpm (WD136BA by WD) $120.00 13.0
216 15 GB 9.5ms 5400 rpm Ultra ATA66 $117.60 15.0
221 15 GB 9.5ms 5400 rpm Ultra ATA66 (WD153AA by WD) $108.00 15.0
217 15 GB 9ms 7200 rpm Ultra ATA66 by Maxtor $117.60 15.0
207 15 GB 9ms 7200 rpm Ultra ATA66 WD $126.00 15.0
226 15GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307015 by IBM) $151.20 15.0
220 17GB 5400 rpm (MPD4173AT by fujitsu) $128.40 17.0
214 20.5 GB 9.5ms 5400 rpm Ultra ATA66 by WD $126.00 20.5
222 20.5 GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307020 by IBM) $150.00 20.5
199 20.5 GB 9ms 7200 rpm Ultra ATA66 (AC205BA by WD) $141.60 20.5
210 30 GB 5400 RPM 9.5ms $141.60 30.0
211 30 GB 7200 RPM 9ms ( MX54098U6 by Maxtor) $168.00 30.0
224 30 GB 7200 RPM 9ms Ultra ATA66 2M cache ( DTLA307030 by IBM) $174.00 30.0
202 40 GB 9ms 7200 rpm Ultra ATA66 $217.20 40.0
227 40GB 9ms 7200 rpm Ultra ATA66 (MX54098U8 by Maxtor) $217.20 40.0
215 45GB 5400 RPM 9.5ms (WD450AA by WD) $168.00 45.0
223 45GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307045 by IBM) $228.00 45.0
225 60GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307060 by IBM) $414.00 60.0
228 61GB 9.5ms 7200rpm Ultra ATA66 (MX96147U8 by Maxtor) $258.00 61.0
Total Records: 60
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 3.22.32
Type 'help' for help.
mysql> SELECT i.item_id, i.name, i.price, sd.spec+0.0 as capacity
-> FROM item i, spec_def sd, spec s1, spec s2, cat c
-> WHERE i.item_id = s1.item_id
-> AND s1.item_id = s2.item_id
-> AND s2.desc_id = 26
-> AND s1.desc_id = 23
-> AND i.cat_id = c.cat_id
-> AND c.maincat_id = 5
-> AND s2.spec_id = sd.spec_id
-> AND s1.spec_id = 72
-> ORDER BY capacity, name;
+---------+---------------------------------------------------------------+--------+----------+
| item_id | name | price | capacity |
+---------+---------------------------------------------------------------+--------+----------+
| 208 | 4.3 GB 5400 RPM 9.5ms WD | 84.00 | 4.3 |
| 204 | 4.3GB 5400 rpm Fujitsu | 81.60 | 4.3 |
| 205 | 6.4GB 5400 rpm (AC36400A/26400A by WD) | 103.20 | 6.4 |
| 219 | 6.4GB 5400 rpm (MPB/MPC by fujitsu) | 84.00 | 6.4 |
| 209 | 8.4 GB 5400 RPM 9.5ms WD | 91.20 | 8.4 |
| 203 | 8.4GB 5400 rpm (MPBMPC by Fujitsu) | 94.80 | 8.4 |
| 229 | 10 GB 5400 rpm 9.5ms Ultra ATA66 (MX91021/80/24 by Maxtor) | 111.60 | 10.0 |
| 198 | 10 GB 5400 rpm 9.5ms Ultra ATA66 conner | 85.20 | 10.0 |
| 230 | 10.2 GB 9ms 7200 rpm Ultra ATA66 ( mx51024/91024u3 BY Maxtor) | 108.00 | 10.2 |
| 201 | 10.2 GB 9ms 7200 rpm Ultra ATA66 by Fujitsu | 104.40 | 10.2 |
| 218 | 13GB 5400 rpm (AC313000A by WD) | 104.40 | 13.0 |
| 236 | 13GB 7200 rpm (WD136BA by WD) | 120.00 | 13.0 |
| 216 | 15 GB 9.5ms 5400 rpm Ultra ATA66 | 117.60 | 15.0 |
| 221 | 15 GB 9.5ms 5400 rpm Ultra ATA66 (WD153AA by WD) | 108.00 | 15.0 |
| 217 | 15 GB 9ms 7200 rpm Ultra ATA66 by Maxtor | 117.60 | 15.0 |
| 207 | 15 GB 9ms 7200 rpm Ultra ATA66 WD | 126.00 | 15.0 |
| 226 | 15GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307015 by IBM) | 151.20 | 15.0 |
| 220 | 17GB 5400 rpm (MPD4173AT by fujitsu) | 128.40 | 17.0 |
| 214 | 20.5 GB 9.5ms 5400 rpm Ultra ATA66 by WD | 126.00 | 20.5 |
| 222 | 20.5 GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307020 by IBM) | 150.00 | 20.5 |
| 199 | 20.5 GB 9ms 7200 rpm Ultra ATA66 (AC205BA by WD) | 141.60 | 20.5 |
| 210 | 30 GB 5400 RPM 9.5ms | 141.60 | 30.0 |
| 211 | 30 GB 7200 RPM 9ms ( MX54098U6 by Maxtor) | 168.00 | 30.0 |
| 224 | 30 GB 7200 RPM 9ms Ultra ATA66 2M cache ( DTLA307030 by IBM) | 174.00 | 30.0 |
| 202 | 40 GB 9ms 7200 rpm Ultra ATA66 | 217.20 | 40.0 |
| 227 | 40GB 9ms 7200 rpm Ultra ATA66 (MX54098U8 by Maxtor) | 217.20 | 40.0 |
| 215 | 45GB 5400 RPM 9.5ms (WD450AA by WD) | 168.00 | 45.0 |
| 223 | 45GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307045 by IBM) | 228.00 | 45.0 |
| 225 | 60GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307060 by IBM) | 414.00 | 60.0 |
| 228 | 61GB 9.5ms 7200rpm Ultra ATA66 (MX96147U8 by Maxtor) | 258.00 | 61.0 |
+---------+---------------------------------------------------------------+--------+----------+
30 rows in set (0.02 sec)
mysql>
mysql> show tables;
+----------------------+
| Tables in thorr_test |
+----------------------+
| cat |
| comp_def |
| desc_def |
| item |
| main_desc |
| maincat |
| spec |
| spec_def |
| user |
+----------------------+
9 rows in set (0.00 sec)
mysql> desc cat;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| cat_id | int(6) | | PRI | 0 | auto_increment |
| name | varchar(32) | | | | |
| cat_desc | varchar(64) | YES | | NULL | |
| maincat_id | int(6) | | | 0 | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc comp_def;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| cat_id1 | int(6) | | PRI | 0 | |
| cat_id2 | int(6) | | PRI | 0 | |
| desc_id1 | int(6) | | PRI | 0 | |
| desc_id2 | int(6) | | PRI | 0 | |
| method | char(2) | | | | |
+----------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> desc desc_def;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| desc_id | int(6) | | PRI | 0 | auto_increment |
| description | varchar(64) | | | | |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc item;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| item_id | int(11) | | PRI | 0 | auto_increment |
| name | varchar(64) | | | | |
| short_desc | varchar(128) | YES | | NULL | |
| long_desc | text | YES | | NULL | |
| image | varchar(64) | YES | | NULL | |
| img_small | varchar(64) | YES | | NULL | |
| price | float(8,2) | YES | | NULL | |
| cat_id | int(8) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> desc main_desc;
+-------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+----------------+
| maindesc_id | int(6) | | PRI | 0 | auto_increment |
| maincat_id | int(6) | YES | | 0 | |
| desc_id | int(6) | | | 0 | |
| cat_id | int(6) | YES | | 0 | |
+-------------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc maincat;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| maincat_id | int(6) | | MUL | 0 | auto_increment |
| name | varchar(32) | | | | |
| maincat_desc | varchar(64) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc spec;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| sp_id | int(10) | | PRI | 0 | auto_increment |
| item_id | int(11) | | | 0 | |
| desc_id | int(6) | | | 0 | |
| spec_id | int(8) | | | 0 | |
+---------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc spec_def;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| spec_id | int(8) | | PRI | 0 | auto_increment |
| spec | varchar(64) | | | | |
| desc_id | int(6) | | | 0 | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| uid | int(6) | | PRI | 0 | auto_increment |
| username | varchar(32) | | | | |
| password | varchar(64) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
I have a strange problem. When I execute a query on mysql console it gives me 30 rows. But if I execute the same query on php I got 60 records (verified by mysql_num_rows). I had about 20 queries and only that specific one has that problem. If there is a known problem or a common error please let me know.
Here are my php code, html output and mysql output. I include db information at the end, but did not include any db data.
Thanks all in advance.
Tolga "thorr" Orhon
PS: I am submitting this problem for the second time as a new thread to put in compact form. Sorry for any inconvenience I may cause.
<BODY>
<TABLE>
<TR>
<TD WIDTH=50>ID</TD>
<TD WIDTH=200>PRODUCT</TD>
<TD WIDTH=75>PRICE</TD>
<TD WIDTH=50>CAPACITY</TD>
</TR>
<?
//Server Connection
if(!($dbLink = mysql_pconnect("localhost","root","tfa123././")))
{
print("Failed to connect to database!<BR>\n");
exit();
}
//select database
if(!mysql_select_db("thorr_test",$dbLink))
{
print("Can't use database!<BR>\n");
exit();
}
$qryIdeHdd = "SELECT i.item_id, i.name, i.price, sd.spec+0.0 as capacity ";
$qryIdeHdd .= "FROM item i, spec_def sd, spec s1, spec s2, cat c ";
$qryIdeHdd .= "WHERE i.item_id = s1.item_id ";
$qryIdeHdd .= " AND s1.item_id = s2.item_id ";
$qrtIdeHdd .= " AND s2.desc_id = 26 ";
$qryIdeHdd .= " AND s1.desc_id = 23 ";
$qryIdeHdd .= " AND i.cat_id = c.cat_id ";
$qryIdeHdd .= " AND c.maincat_id = 5 ";
$qryIdeHdd .= " AND s2.spec_id = sd.spec_id ";
$qryIdeHdd .= " AND s1.spec_id = 72 ";
$qryIdeHdd .= "ORDER BY capacity, name;";
if(!($recIdeHdd=mysql_query($qryIdeHdd, $dbLink)))
{
print("Query error!...<BR>\n");
print("MySQL say : " . mysql_error() . "<BR>\n");
print("Query was : $qryIdeHdd<BR>\n");
exit();
}
$intRecNum = mysql_num_rows($recIdeHdd);
while($rowIdeHdd = mysql_fetch_object($recIdeHdd))
{
print("<TR>\n<TD>$rowIdeHdd->item_id</TD>\n<TD>$rowIdeHdd->name</TD>\n");
print("<TD>$$rowIdeHdd->price</TD>\n<TD>$rowIdeHdd->capacity</TD>\n</TR>\n");
}
print("<TR><TD COLSPAN=4>Total Records: $intRecNum </TD></TR>\n");
?>
</TABLE>
</BODY>
ID PRODUCT PRICE CAPACITY
229 10 GB 5400 rpm 9.5ms Ultra ATA66 (MX91021/80/24 by Maxtor) $111.60 0.0
198 10 GB 5400 rpm 9.5ms Ultra ATA66 conner $85.20 0.0
230 10.2 GB 9ms 7200 rpm Ultra ATA66 ( mx51024/91024u3 BY Maxtor) $108.00 0.0
201 10.2 GB 9ms 7200 rpm Ultra ATA66 by Fujitsu $104.40 0.0
218 13GB 5400 rpm (AC313000A by WD) $104.40 0.0
236 13GB 7200 rpm (WD136BA by WD) $120.00 0.0
216 15 GB 9.5ms 5400 rpm Ultra ATA66 $117.60 0.0
221 15 GB 9.5ms 5400 rpm Ultra ATA66 (WD153AA by WD) $108.00 0.0
217 15 GB 9ms 7200 rpm Ultra ATA66 by Maxtor $117.60 0.0
207 15 GB 9ms 7200 rpm Ultra ATA66 WD $126.00 0.0
226 15GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307015 by IBM) $151.20 0.0
220 17GB 5400 rpm (MPD4173AT by fujitsu) $128.40 0.0
214 20.5 GB 9.5ms 5400 rpm Ultra ATA66 by WD $126.00 0.0
222 20.5 GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307020 by IBM) $150.00 0.0
199 20.5 GB 9ms 7200 rpm Ultra ATA66 (AC205BA by WD) $141.60 0.0
210 30 GB 5400 RPM 9.5ms $141.60 0.0
211 30 GB 7200 RPM 9ms ( MX54098U6 by Maxtor) $168.00 0.0
224 30 GB 7200 RPM 9ms Ultra ATA66 2M cache ( DTLA307030 by IBM) $174.00 0.0
208 4.3 GB 5400 RPM 9.5ms WD $84.00 0.0
204 4.3GB 5400 rpm Fujitsu $81.60 0.0
202 40 GB 9ms 7200 rpm Ultra ATA66 $217.20 0.0
227 40GB 9ms 7200 rpm Ultra ATA66 (MX54098U8 by Maxtor) $217.20 0.0
215 45GB 5400 RPM 9.5ms (WD450AA by WD) $168.00 0.0
223 45GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307045 by IBM) $228.00 0.0
205 6.4GB 5400 rpm (AC36400A/26400A by WD) $103.20 0.0
219 6.4GB 5400 rpm (MPB/MPC by fujitsu) $84.00 0.0
225 60GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307060 by IBM) $414.00 0.0
228 61GB 9.5ms 7200rpm Ultra ATA66 (MX96147U8 by Maxtor) $258.00 0.0
209 8.4 GB 5400 RPM 9.5ms WD $91.20 0.0
203 8.4GB 5400 rpm (MPBMPC by Fujitsu) $94.80 0.0
208 4.3 GB 5400 RPM 9.5ms WD $84.00 4.3
204 4.3GB 5400 rpm Fujitsu $81.60 4.3
205 6.4GB 5400 rpm (AC36400A/26400A by WD) $103.20 6.4
219 6.4GB 5400 rpm (MPB/MPC by fujitsu) $84.00 6.4
209 8.4 GB 5400 RPM 9.5ms WD $91.20 8.4
203 8.4GB 5400 rpm (MPBMPC by Fujitsu) $94.80 8.4
229 10 GB 5400 rpm 9.5ms Ultra ATA66 (MX91021/80/24 by Maxtor) $111.60 10.0
198 10 GB 5400 rpm 9.5ms Ultra ATA66 conner $85.20 10.0
230 10.2 GB 9ms 7200 rpm Ultra ATA66 ( mx51024/91024u3 BY Maxtor) $108.00 10.2
201 10.2 GB 9ms 7200 rpm Ultra ATA66 by Fujitsu $104.40 10.2
218 13GB 5400 rpm (AC313000A by WD) $104.40 13.0
236 13GB 7200 rpm (WD136BA by WD) $120.00 13.0
216 15 GB 9.5ms 5400 rpm Ultra ATA66 $117.60 15.0
221 15 GB 9.5ms 5400 rpm Ultra ATA66 (WD153AA by WD) $108.00 15.0
217 15 GB 9ms 7200 rpm Ultra ATA66 by Maxtor $117.60 15.0
207 15 GB 9ms 7200 rpm Ultra ATA66 WD $126.00 15.0
226 15GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307015 by IBM) $151.20 15.0
220 17GB 5400 rpm (MPD4173AT by fujitsu) $128.40 17.0
214 20.5 GB 9.5ms 5400 rpm Ultra ATA66 by WD $126.00 20.5
222 20.5 GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307020 by IBM) $150.00 20.5
199 20.5 GB 9ms 7200 rpm Ultra ATA66 (AC205BA by WD) $141.60 20.5
210 30 GB 5400 RPM 9.5ms $141.60 30.0
211 30 GB 7200 RPM 9ms ( MX54098U6 by Maxtor) $168.00 30.0
224 30 GB 7200 RPM 9ms Ultra ATA66 2M cache ( DTLA307030 by IBM) $174.00 30.0
202 40 GB 9ms 7200 rpm Ultra ATA66 $217.20 40.0
227 40GB 9ms 7200 rpm Ultra ATA66 (MX54098U8 by Maxtor) $217.20 40.0
215 45GB 5400 RPM 9.5ms (WD450AA by WD) $168.00 45.0
223 45GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307045 by IBM) $228.00 45.0
225 60GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307060 by IBM) $414.00 60.0
228 61GB 9.5ms 7200rpm Ultra ATA66 (MX96147U8 by Maxtor) $258.00 61.0
Total Records: 60
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 3.22.32
Type 'help' for help.
mysql> SELECT i.item_id, i.name, i.price, sd.spec+0.0 as capacity
-> FROM item i, spec_def sd, spec s1, spec s2, cat c
-> WHERE i.item_id = s1.item_id
-> AND s1.item_id = s2.item_id
-> AND s2.desc_id = 26
-> AND s1.desc_id = 23
-> AND i.cat_id = c.cat_id
-> AND c.maincat_id = 5
-> AND s2.spec_id = sd.spec_id
-> AND s1.spec_id = 72
-> ORDER BY capacity, name;
+---------+---------------------------------------------------------------+--------+----------+
| item_id | name | price | capacity |
+---------+---------------------------------------------------------------+--------+----------+
| 208 | 4.3 GB 5400 RPM 9.5ms WD | 84.00 | 4.3 |
| 204 | 4.3GB 5400 rpm Fujitsu | 81.60 | 4.3 |
| 205 | 6.4GB 5400 rpm (AC36400A/26400A by WD) | 103.20 | 6.4 |
| 219 | 6.4GB 5400 rpm (MPB/MPC by fujitsu) | 84.00 | 6.4 |
| 209 | 8.4 GB 5400 RPM 9.5ms WD | 91.20 | 8.4 |
| 203 | 8.4GB 5400 rpm (MPBMPC by Fujitsu) | 94.80 | 8.4 |
| 229 | 10 GB 5400 rpm 9.5ms Ultra ATA66 (MX91021/80/24 by Maxtor) | 111.60 | 10.0 |
| 198 | 10 GB 5400 rpm 9.5ms Ultra ATA66 conner | 85.20 | 10.0 |
| 230 | 10.2 GB 9ms 7200 rpm Ultra ATA66 ( mx51024/91024u3 BY Maxtor) | 108.00 | 10.2 |
| 201 | 10.2 GB 9ms 7200 rpm Ultra ATA66 by Fujitsu | 104.40 | 10.2 |
| 218 | 13GB 5400 rpm (AC313000A by WD) | 104.40 | 13.0 |
| 236 | 13GB 7200 rpm (WD136BA by WD) | 120.00 | 13.0 |
| 216 | 15 GB 9.5ms 5400 rpm Ultra ATA66 | 117.60 | 15.0 |
| 221 | 15 GB 9.5ms 5400 rpm Ultra ATA66 (WD153AA by WD) | 108.00 | 15.0 |
| 217 | 15 GB 9ms 7200 rpm Ultra ATA66 by Maxtor | 117.60 | 15.0 |
| 207 | 15 GB 9ms 7200 rpm Ultra ATA66 WD | 126.00 | 15.0 |
| 226 | 15GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307015 by IBM) | 151.20 | 15.0 |
| 220 | 17GB 5400 rpm (MPD4173AT by fujitsu) | 128.40 | 17.0 |
| 214 | 20.5 GB 9.5ms 5400 rpm Ultra ATA66 by WD | 126.00 | 20.5 |
| 222 | 20.5 GB 9ms 7200 rpm 2M cache Ultra ATA66 (DTLA307020 by IBM) | 150.00 | 20.5 |
| 199 | 20.5 GB 9ms 7200 rpm Ultra ATA66 (AC205BA by WD) | 141.60 | 20.5 |
| 210 | 30 GB 5400 RPM 9.5ms | 141.60 | 30.0 |
| 211 | 30 GB 7200 RPM 9ms ( MX54098U6 by Maxtor) | 168.00 | 30.0 |
| 224 | 30 GB 7200 RPM 9ms Ultra ATA66 2M cache ( DTLA307030 by IBM) | 174.00 | 30.0 |
| 202 | 40 GB 9ms 7200 rpm Ultra ATA66 | 217.20 | 40.0 |
| 227 | 40GB 9ms 7200 rpm Ultra ATA66 (MX54098U8 by Maxtor) | 217.20 | 40.0 |
| 215 | 45GB 5400 RPM 9.5ms (WD450AA by WD) | 168.00 | 45.0 |
| 223 | 45GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307045 by IBM) | 228.00 | 45.0 |
| 225 | 60GB 9ms 7200 rpm Ultra ATA66 2M cache (DTLA307060 by IBM) | 414.00 | 60.0 |
| 228 | 61GB 9.5ms 7200rpm Ultra ATA66 (MX96147U8 by Maxtor) | 258.00 | 61.0 |
+---------+---------------------------------------------------------------+--------+----------+
30 rows in set (0.02 sec)
mysql>
mysql> show tables;
+----------------------+
| Tables in thorr_test |
+----------------------+
| cat |
| comp_def |
| desc_def |
| item |
| main_desc |
| maincat |
| spec |
| spec_def |
| user |
+----------------------+
9 rows in set (0.00 sec)
mysql> desc cat;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| cat_id | int(6) | | PRI | 0 | auto_increment |
| name | varchar(32) | | | | |
| cat_desc | varchar(64) | YES | | NULL | |
| maincat_id | int(6) | | | 0 | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc comp_def;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| cat_id1 | int(6) | | PRI | 0 | |
| cat_id2 | int(6) | | PRI | 0 | |
| desc_id1 | int(6) | | PRI | 0 | |
| desc_id2 | int(6) | | PRI | 0 | |
| method | char(2) | | | | |
+----------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> desc desc_def;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| desc_id | int(6) | | PRI | 0 | auto_increment |
| description | varchar(64) | | | | |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc item;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| item_id | int(11) | | PRI | 0 | auto_increment |
| name | varchar(64) | | | | |
| short_desc | varchar(128) | YES | | NULL | |
| long_desc | text | YES | | NULL | |
| image | varchar(64) | YES | | NULL | |
| img_small | varchar(64) | YES | | NULL | |
| price | float(8,2) | YES | | NULL | |
| cat_id | int(8) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> desc main_desc;
+-------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+----------------+
| maindesc_id | int(6) | | PRI | 0 | auto_increment |
| maincat_id | int(6) | YES | | 0 | |
| desc_id | int(6) | | | 0 | |
| cat_id | int(6) | YES | | 0 | |
+-------------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc maincat;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| maincat_id | int(6) | | MUL | 0 | auto_increment |
| name | varchar(32) | | | | |
| maincat_desc | varchar(64) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc spec;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| sp_id | int(10) | | PRI | 0 | auto_increment |
| item_id | int(11) | | | 0 | |
| desc_id | int(6) | | | 0 | |
| spec_id | int(8) | | | 0 | |
+---------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc spec_def;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| spec_id | int(8) | | PRI | 0 | auto_increment |
| spec | varchar(64) | | | | |
| desc_id | int(6) | | | 0 | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| uid | int(6) | | PRI | 0 | auto_increment |
| username | varchar(32) | | | | |
| password | varchar(64) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>