Search the Community

Hello guys. Please do me a favor and help me solve this slow query Tables: CREATE TABLE `products` ( `ProductId` int(11) NOT NULL AUTO_INCREMENT, `CategoryId` int(11) NOT NULL, `BrandId` int(11) DEFAULT NULL, `PCode` varchar(100) COLLATE utf8_persian_ci NOT NULL, `PName` varchar(255) COLLATE utf8_persian_ci NOT NULL, `UnitId` int(11) DEFAULT NULL, `PNameEn` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL, `PShortName` varchar(100) COLLATE utf8_persian_ci DEFAULT NULL, `PDescription` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL, `Weight` int(11) DEFAULT NULL, `PImage` varchar(100) COLLATE utf8_persian_ci NOT NULL DEFAULT 'noimage.png', `PIcon` varchar(100) COLLATE utf8_persian_ci DEFAULT 'noicon.png', `PGallery` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL, `PDetail` text COLLATE utf8_persian_ci, `PTags` text COLLATE utf8_persian_ci, `PSpecial` tinyint(4) NOT NULL DEFAULT '0', `PView` int(11) NOT NULL DEFAULT '0', `Status` tinyint(2) DEFAULT '1' COMMENT '0: inactive. 1: active. 5: deleted.', `BasePrice` int(11) DEFAULT NULL, `PCreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `PUpdateDate` datetime NOT NULL, `PUrl` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL, `ProviderName` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL, PRIMARY KEY (`ProductId`), UNIQUE KEY `PCode` (`PCode`), KEY `CategoryId` (`CategoryId`), KEY `BrandId` (`BrandId`), KEY `PName` (`PName`) ) ENGINE=MyISAM AUTO_INCREMENT=341028 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `units` ( `UnitId` int(11) NOT NULL AUTO_INCREMENT, `UnitName` varchar(25) COLLATE utf8_persian_ci NOT NULL, PRIMARY KEY (`UnitId`) ) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `products_sub` ( `ProductSubId` int(11) NOT NULL AUTO_INCREMENT, `ProviderId` int(11) NOT NULL, `PCode` varchar(100) COLLATE utf8_persian_ci NOT NULL, `Price` int(11) NOT NULL, `ProviderPrice` int(11) NOT NULL, `PQuantity` int(10) NOT NULL, `PDeliveryTime` time NOT NULL, `PEditTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`ProductSubId`), KEY `ProviderId` (`ProviderId`), KEY `PCode` (`PCode`) ) ENGINE=MyISAM AUTO_INCREMENT=340349 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `provider` ( `ProviderId` int(11) NOT NULL AUTO_INCREMENT, `UserId` int(11) NOT NULL, `ProviderName` varchar(25) COLLATE utf8_persian_ci NOT NULL, `ProviderPhone` varchar(11) COLLATE utf8_persian_ci NOT NULL, `ProviderCell` varchar(11) COLLATE utf8_persian_ci NOT NULL, `ProviderAddress` varchar(250) COLLATE utf8_persian_ci NOT NULL, `ProviderDetail` text COLLATE utf8_persian_ci NOT NULL, `Email` varchar(50) COLLATE utf8_persian_ci NOT NULL, `Status` tinyint(1) NOT NULL, `RequestTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `Permission` varchar(250) COLLATE utf8_persian_ci NOT NULL, PRIMARY KEY (`ProviderId`), UNIQUE KEY `UserId` (`UserId`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `order_products` ( `OrderProductId` int(11) NOT NULL AUTO_INCREMENT, `OrderId` int(11) NOT NULL, `ProviderId` int(11) NOT NULL, `PCode` varchar(50) COLLATE utf8_persian_ci NOT NULL, `PName` varchar(50) COLLATE utf8_persian_ci NOT NULL, `PUnit` varchar(50) COLLATE utf8_persian_ci NOT NULL, `Token` int(11) NOT NULL DEFAULT '999', `ProductPrice` int(11) NOT NULL, `ProductWeight` int(11) NOT NULL, `ProductPurchase` int(11) NOT NULL, PRIMARY KEY (`OrderProductId`), KEY `ProductId` (`PCode`), KEY `OrderId` (`OrderId`), KEY `ProviderId` (`ProviderId`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `brands` ( `BrandId` int(11) NOT NULL AUTO_INCREMENT, `BrandName` varchar(25) NOT NULL, `BrandEn` varchar(25) DEFAULT NULL, PRIMARY KEY (`BrandId`), UNIQUE KEY `BrandName` (`BrandName`) ) ENGINE=MyISAM AUTO_INCREMENT=1988 DEFAULT CHARSET=utf8 My Query: SELECT `products`.`ProductId`, `products`.`PName`, `products`.`Status`, `products`.`PSpecial`, `products`.`PImage`, `products`.`BasePrice`, `products`.`CategoryId`, `units`.`UnitName`, `products_sub`.`ProductSubId`, `products_sub`.`ProviderId`, `products_sub`.`PQuantity`, `products_sub`.`ProviderPrice`, `products_sub`.`Price` AS `customerPrice`, `provider`.`ProviderName`, SUM(`order_products`.`ProductPurchase`) AS `sale` FROM `products` LEFT JOIN `brands` ON `products`.`BrandId`=`brands`.`BrandId` INNER JOIN `units` ON `products`.`UnitId`=`units`.`UnitId` LEFT JOIN `products_sub` ON `products`.`PCode`=`products_sub`.`PCode` AND `products_sub`.`ProductSubId` = ( SELECT `ProductSubId` FROM `products_sub` WHERE `PCode`=`products`.`PCode` AND `PQuantity` > 0 ORDER BY `ProviderPrice` LIMIT 1 ) LEFT JOIN `provider` ON `products_sub`.`ProviderId`=`provider`.`ProviderId` LEFT JOIN `order_products` ON `products`.`PCode`=`order_products`.`PCode` WHERE `products`.`Status`>0 AND `products`.`Status`!=5 AND `products`.`CategoryId` IN (1133,1134,1137,1174,1175,1176,1138,1177,1178,1179,1139,1140,1141,1180,1181,1182,1183,1184,1142,1143,1144,1145,1185,1186,1187,1188,1189,1190,1191,1146,1147,1148,1149,1150,1192,1193,1194,1855,1856,1857,1135,1151,1152,1153,1154,1155,1156,1157,1158,1136,1159,1160,1161,1162,1163,1164,1165,1166,1195,1196,1197,1198,1199,1167,1168,1200,1201,1202,1203,1204,1205,1169,1206,1207,1208,1209,1170,1171,1172,1173,1210,1211,1858,1859,1212,1213,1214,1232,1233,1234,1215,1216,1235,1236,1237,1238,1217,1239,1240,1241,1242,1218,1219,1220,1243,1244,1245,1246,1247,1248,1221,1249,1250,1251,1222,1223,1224,1252,1253,1254,1225,1255,1256,1257,1258,1259,1260,1261,1262,1263,1226,1227,1228,1229,1230,1231,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1463,1464,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1465,1466,1467,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1468,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1469,1470,1471,1472,1473,1474,1508,1509,1286,1287,1290,1299,1395,1396,1300,1301,1397,1398,1399,1302,1303,1304,1305,1400,1401,1402,1306,1307,1308,1309,1403,1404,1310,1405,1406,1407,1408,1311,1312,1313,1314,1315,1409,1410,1291,1316,1317,1318,1319,1320,1292,1321,1322,1323,1324,1288,1293,1325,1411,1412,1326,1327,1413,1414,1328,1329,1330,1331,1415,1416,1417,1418,1332,1419,1420,1421,1422,1333,1334,1335,1423,1424,1336,1425,1426,1427,1428,1429,1430,1431,1337,1338,1432,1433,1434,1339,1340,1341,1342,1435,1436,1437,1343,1438,1439,1440,1441,1294,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1295,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1860,1861,1862,1289,1296,1364,1442,1443,1444,1365,1445,1446,1447,1448,1366,1367,1368,1369,1449,1450,1451,1452,1453,1370,1371,1372,1373,1454,1455,1456,1457,1458,1459,1374,1375,1460,1376,1377,1378,1379,1461,1462,1297,1380,1381,1382,1383,1384,1385,1386,1298,1387,1388,1389,1390,1391,1392,1393,1394,1863,1864,1865,1132) GROUP BY `products`.`PCode` ORDER BY `products`.`Status`, `products_sub`.`PQuantity` > 0 DESC, `products`.`PSpecial` DESC, `customerPrice` ASC, `products`.`BasePrice` ASC LIMIT 36 OFFSET 0 This query is extremely slow When is remove the ORDER BY section, It will run very fast. As i researched, its better to use sub-queries. but didn't figure out how to use them with left join. Any help is appreciated. thanks.

hello I am looking for help on a sql problem. Not sure if I have a statement mixed up or not but the table simply won't render. Any help would be greatly appreciated. Database name = test I have two tables table 1 = parts table 2 = parts_pricing The main recordset name is RSparts Please see my current php code - $maxRows_RSparts = 10; $pageNum_RSparts = 0; if (isset($_GET['pageNum_RSparts'])) { $pageNum_RSparts = $_GET['pageNum_RSparts']; } $startRow_RSparts = $pageNum_RSparts * $maxRows_RSparts; $colname1_RSparts = "-1"; if (isset($_GET['figno'])) { $colname1_RSparts = $_GET['figno']; } $colname2_RSparts = "-1"; if (isset($_GET['modelno'])) { $colname2_RSparts = $_GET['modelno']; } mysql_select_db($database_test, $test); $query_RSparts = sprintf("SELECT * FROM parts INNER JOIN partspricing ON partspricing.sellingprice=parts.partID WHERE figno = %s AND modelno = %s ORDERBY ACS ", GetSQLValueString($colname1_RSparts, "text"),GetSQLValueString($colname2_RSparts, "text")); $query_limit_RSparts = sprintf("%s LIMIT %d, %d", $query_RSparts, $startRow_RSparts, $maxRows_RSparts); $RSparts = mysql_query($query_limit_RSRSparts, $test) or die(mysql_error()); $row_RSparts = mysql_fetch_assoc($RSparts; if (isset($_GET['totalRows_RSparts'])) { $totalRows_RSparts = $_GET['totalRows_RSparts']; } else { $all_RSparts = mysql_query($query_RSparts, $test); $totalRows_RSRSparts = mysql_num_rows($all_RSparts); } $totalPages_RSparts = ceil($totalRows_RSparts/$maxRows_RSparts)-1; Table setup; <table> <tr> <td>Key No.</td> <td>Part Number</td> <td>Description</td> <td>QTY</td> <td>S/N Break</td> <td>Availabilty</td> <td>Price</td> </tr> <?php do { ?> <tr> <td><?php echo $row_RSparts['keyno']; ?></td> <td><?php echo $row_RSparts['partno']; ?></td> <td><?php echo $row_RSparts['description']; ?></td> <td><?php echo $row_RSparts['qty']; ?></td> <td><?php echo $row_RSparts['snbreak']; ?></td> <td><?php echo $row_RSparts['availability']; ?></td> <td><?php echo $row_RSparts['sellingprice']; ?></td> </tr> <?php } while ($row_RSparts = mysql_fetch_assoc($RSparts)); ?> If I leave out parts INNER JOIN partspricing ON partspricing.sellingprice=parts.partID the table works and renders fine or if I leave out WHERE figno = %s AND modelno = %s ORDERBY ACS the table renders fine. I am missing something here. If I leave it as is the table will not render at all. Hopefully this is something simple any help would be greatly appreciated!

I am trying to get all the "posts" from a users friends list on their news feed. I'm trying to do so by getting all those posts where the user is the initiator_user_id and get all the posts from the friend_user_id then I want to get all the posts from initiator_user_id where the user's id is the friend_user_id. This is what I have that's messing up: $wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n1 ON (n1.initiator_user_id=f.user_id) INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id) WHERE (n1.friend_user_id=:userid) OR (n2.initiator_user_id=:userid) ORDER BY datetime DESC LIMIT 8'); $wallsql->bindParam(':userid', $_SESSION['uid']); $wallsql->execute(); Previously this works but it only gets the posts where the user is the initiator_user_id: $wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n2 ON n2.friend_user_id=f.user_id WHERE n2.initiator_user_id=:userid ORDER BY datetime DESC LIMIT 8'); Any help would be much appreciated!

I have been trying to figure this out for days but no luck so far. I have two tables which I want to pull info from and from what I read a join query is the way to go. I need to pull the info from the bank table based on the login id in the users table. I keep getting a black page result or a Data not Found result. Because this is the first time I have tried a join query I don't really know what I am doing. Is anyone able to give me some advice? <?php //if the login session does not exist therefore meaning the user is not logged in if(strcmp($_SESSION['uid'],"") == 0){ //display and error message header('Location: ../index.php'); }else{ //otherwise continue the page //this is out update script which should be used in each page to update the users online time $time = date('U')+50; $update = mysql_query("UPDATE `e-users` SET `Online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'"); ?> <?php //make sure you close the check if their online } ?> <?php require("../edb.php"); $result = mysql_query("SELECT AcctName, BSB1, BSB2, AccNumber, Adviser, Bank FROM a-bank JOIN e-users ON a-bank.Adviser = e-users.id; WHERE e-users.id = '".$_SESSION['uid']."'"); if (!$result) { die("Error: Data not found.."); } $AcctName=$test['AcctName']; ?>

please help i have assignment to selecting tables by joining them.. how do i use join, or inner join? is there another option selecting 3 tables by using foreign key? Here's the table this one is user table here is article category and this is content of article i was willing to choosing article_news table = article_id, category_article_id, user_type_id, user_id, judul(title), artikel, photo, tag, tanggal tcategory_article = category_article_id, category_article tuser = user_type_id, firstname, lastname thank you