kickstart

Hi As it is inner joins it wouldn't matter. The SQL above could easily bring back the same supplier multiple times and the same product back once for each supplier. Only real issue is ensuring that the quantity ranges do not overlap for any single product for a single supplier. All the best Keith

Hi OK, no probs. Assuming a lower level of knowledge. A table should have a primary key which is unique to each row. Sometimes this can be meaningful but often it is just a sequentially generated number. For generated numbers you can use an auto_increment value. By default this is just 1 for the first record, 2 for the next, etc. The actual value is unimportant (and you shouldn't rely on it being in any particular pattern), as long as it is unique. If you are using such a primary key you do not need to specify a value for it on the INSERT. Either do not specify that columns value or specify it as NULL (which is what I tend to do). In this case mysql just uses the next available value. If you do try and specify a value then you need one that hasn't been used before. And this might not be as trivial as you might think (ie, someone else could do an insert between you finding the next value and doing the insert). As it should be meaningless it is best to just let mysql determine the value itself. If you have an existing record which you want to change the values in then you use an UPDATE statement rather than an INSERT. With an UPDATE you need to specify the record(s) that you want to update. If it is a single record then you can just use the primary key value to uniquely identify they record to be updated. All the best Keith

Hi Use the 2 UNIONed queries in the FROM clause of a SELECT, JOINed with the other table you want. I have no idea what you want from the 2nd table, but something like this would do it. SELECT a.firstname, a.lastname, a.username, a.accounttype, a.country, a.users, a.`state`, a.users, a.city, a.approved_date, a.total_a, a.total_b, b.somefield FROM (( SELECT `users`.`firstname`, `users`.`lastname`, `users`.`username`, `users`.`accounttype`, `users`.`country`, `users`.`state`, `users`.`city`, `partners`.`approved_date`, `current`.`total` AS `total_a`, `friend`.`total` AS `total_b` FROM `partners` INNER JOIN `users` ON `partners`.`user_id` = `users`.`id` LEFT JOIN ( SELECT `user_id` AS `uid`, COUNT(`partner_id`) AS `total` FROM `partners` WHERE `approved` = 1 GROUP BY `user_id` ) AS `current` ON `partners`.`user_id` = `current`.`uid` LEFT JOIN ( SELECT `friend_id` AS `uid`, COUNT(`partner_id`) AS `total` FROM `partners` WHERE `approved` = 1 GROUP BY `friend_id` ) AS `friend` ON `partners`.`user_id` = `friend`.`uid` WHERE `partners`.`friend_id` = ${uid} AND `approved` = 1 ) UNION ALL ( SELECT `users`.`firstname`, `users`.`lastname`, `users`.`username`, `users`.`accounttype`, `users`.`country`, `users`.`state`, `users`.`city`, `partners`.`approved_date`, `current`.`total` AS `total_a`, `friend`.`total` AS `total_b` FROM `partners` INNER JOIN `users` ON `partners`.`friend_id` = `users`.`id` LEFT JOIN ( SELECT `user_id` AS `uid`, COUNT(`partner_id`) AS `total` FROM `partners` WHERE `approved` = 1 GROUP BY `user_id` ) AS `current` ON `partners`.`friend_id` = `current`.`uid` LEFT JOIN ( SELECT `friend_id` AS `uid`, COUNT(`partner_id`) AS `total` FROM `partners` WHERE `approved` = 1 GROUP BY `friend_id` ) AS `friend` ON `partners`.`friend_id` = `friend`.`uid` WHERE `partners`.`user_id` = ${uid} AND `approved` = 1 )) a LEFT OUTER JOIN company_fans b ON a.user_id = b.user_id ORDER BY a.approved_date DESC LIMIT 3 All the best Keith

Hi Quick play and I would have a table of suppliers, a table of products and a table that lists all the product prices for all the suppliers with a high and low number of items for that price. Then you can join the tables together and check that the number required is BETWEEN the lower and upper items limits. For example, after a quick play:- CREATE TABLE IF NOT EXISTS `items` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `ItemName` varchar(50) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; CREATE TABLE IF NOT EXISTS `prices` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `SupplierId` int(11) NOT NULL, `ItemId` int(11) NOT NULL, `LowPriceLimit` int(11) NOT NULL, `HighPriceLimit` int(11) NOT NULL, `Price` int(11) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ; CREATE TABLE IF NOT EXISTS `suppliers` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `SupplierName` varchar(50) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; Then to get all the prices of all the items from all the suppliers where 5 are wanted:- SELECT * FROM suppliers a INNER JOIN prices b ON a.Id = b.SupplierId INNER JOIN items c ON b.ItemId = c.Id WHERE 5 BETWEEN b.LowPriceLimit AND b.HighPriceLimit All the best Keith

Hi Depends what you mean. UNION / UNION ALL joins the results of 2 (or more) queries together and those queries could contain JOINS. Outside of that you could use a LEFT OUTER JOIN to join the results of the UNION (ie, as a subselect) with another table / subselect. All the best Keith

Hi Why are you trying to specify a value on an insert of an autonumber key? All the best Keitrh

Hi To get all the items not returned in that query you would use something like:- SELECT a.* FROM inventory a LEFT OUTER JOIN (SELECT inventory_id FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b ON a.inventory_id = b.inventory_id WHERE b.inventory_id IS NULL However I presume that there are other restriction rather than just all non displayed records. All the best Keith

Hi More of a php question really. However something like this would be required. $sql = 'SELECT ImageName, ImageAltTag FROM Images'; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)) { echo '<img src="'.$row['ImageName'].'" alt="'.$row['ImageAltTag'].'" /><br />'; } That will put out all the images on the database onto the screen. If the images are in a standard sub directory somewhere (say 'images') them just change it to $sql = 'SELECT ImageName, ImageAltTag FROM Images'; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)) { echo '<img src="images/'.$row['ImageName'].'" alt="'.$row['ImageAltTag'].'" /><br />'; } All the best Keith

Hi Variation on monkeytooths suggestion. Have a table of cities and the regions they represent. You would need to set this up in advance (but you will whichever way you use cities). Then if they specify a region use a JOIN. Something like this:- <?php if($param['Submit']) { $tags = ''; if(count($param['tag']) > 1) { foreach($param['tag'] as $v) { $tags .= "tags LIKE '%$v%' AND "; } } else { if($param['tag'] != '') $tags .= "a.tags LIKE '%".$param['tag'][0]."%' AND "; } if(strlen($tags) > 0) $where[] = substr($tags, 0, -5); if($param['time']) { $tmptime = $param['time'] + 100; $where[] = "a.time >= '".$param['time']."' AND a.time <= '".$param['time2']."'"; } if($param['day']) $where[] = "a.day = '".$param['day']."'"; if($param['city']) $where[] = "a.city = '".$param['location']."'"; if($param['region']) $where[] = "b.region = '".$param['region']."'"; $where[] = "a.published = '1'"; if(count($where) > 0) $whereStr = " WHERE " . implode(' AND ',$where). " ORDER BY a.date, a.time ASC"; //die('debug: ' . $whereStr); $sql = "SELECT a.date, a.time, a.meetname, a.address, a.city, a.tags, a.description, a.status, b.region FROM meeting a INNER JOIN cityregions b ON a.city = b.city $whereStr"; $result = mysql_query($sql); $intCounter = 0; $output = "<table width=\"90%\" cellpadding=\"0\" id=\"meeting_t2\" style=\"border: 0\" rules=\"ALL\"><tbody><tr><th>Day</th><th>Time</th><th>Name</th><th>Address</th><th>City</th><th>Tags</th><th>Details</th></tr>"; while($row = mysql_fetch_assoc($result)) { $intCounter++; $output2 = "<tr><td>".$row['date']."</td><td>".date("g:i a", strtotime($row['time']))."</td><td>".$row['meetname']."</td><td> (google maps URL stuff clipped)".$row['address']."</a></td><td>"." ".$row['city']."</td><td>"." ".$row['tags']."</td><td>".$row['description']."</td></tr>"; if($row['status'] == '0') { $output2 = str_replace('<td>','<td><S>',$output2); $output2 = str_replace('</td>','</S></td>',$output2); } $output .= $output2; } $output .= "</tbody></table>"; if($intCounter == 0) { $output .= "No results"; } echo $output; echo "<br /><br /><br />"; } ?> All the best Keith

Hi A table with 1 row per link would be best. This way you can easily join the members table against the link table and back to the members table to get details. All the best Keith

Hi Something like the following would get you a count of all the votes for each post by each user:- SELECT a.users_id, a.UserName, b.PostTitle, COUNT(c.Vote) FROM users a INNER JOIN posts b ON a.users_id = b.users_id INNER JOIN votes c ON b.post_id = c.post_id GROUP BY a.users_id, a.UserName, b.PostTitle However not sure whether this is what you want, or whether you only care about one users posts. All the best Keith

Hi If you want data that is in one but not in the other then you can use something like this:- SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.userid = table2.userid WHERE table2.userid IS NULL To get any record that is on table1 or table2 but not on both can be done in some flavours of SQL using a FULL OUTER JOIN. But MySql doesn't support full outer joins. Probably easiest to use a UNION:- SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.userid = table2.userid WHERE table2.userid IS NULL UNION SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.userid = table2.userid WHERE table1.userid IS NULL All the best Keith

Hi Is it not returning anything or is it giving an error? There is a missing bracket which would result in an error:- SELECT vtp_members.id, vtp_members.name, vtp_members.email, count(vtp_tracking.id) surfs FROM vtp_members, vtp_tracking ON vtp_tracking.credit_members_id=vtp_members.id WHERE YEARWEEK(vtp_tracking.action_date) = YEARWEEK(CURDATE()) GROUP BY vtp_members.id ORDER BY surfs DESC You can put the YEARWEEK into the SELECT clause if you want to check it, but you will also need to put it in the GROUP BY clause. All the best Keith

Hi As above, but what does the function interval do? Should that be intval? All the best Keith

Hi If you can redesign the table to split those columns off to be rows on a separate table. If not then maybe use a SELECT to split them off (possibly use if for a view). Set the following up as a view:- SELECT Id, 'Category_1' AS Category, category_1 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_2' AS Category, category_2 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_3' AS Category, category_3 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_4' AS Category, category_4 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_5' AS Category, category_5 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_6' AS Category, category_6 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_7' AS Category, category_7 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_8' AS Category, category_8 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_9' AS Category, category_9 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_10' AS Category, category_10 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_11' AS Category, category_11 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_12' AS Category, category_12 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_13' AS Category, category_13 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_14' AS Category, category_14 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_15' AS Category, category_15 AS CategoryValue FROM yourtable UNION SELECT Id, 'Category_16' AS Category, category_16 AS CategoryValue FROM yourtable Say call the above MaxCatView SELECT a.Id, a.Category, a.CategoryValue FROM MaxCatView a INNER JOIN (SELECT Id, MAX(CategoryValue) AS MaxCategoryValue FROM MaxCatView GROUP BY Id) b ON a.Id = b.Id AND a.CategoryValue = b.MaxCategoryValue Not tested so please excuse any typos. All the best Keith

Hi The code I gave was to select the rows to bring them back. You should be able to use similar syntax to update the table, but if you do this be very careful and back up the data first. UPDATE wp_posts SET post_contect = LEFT(post_content, INSTR(post_content,'More Useful Tips') -1) All the best Keith

Hi Probably not efficient but this will do it:- SELECT SUBSTR(someurl,8,INSTR(someurl,'.')-, COUNT(*) FROM Places GROUP BY SUBSTR(someurl,8,INSTR(someurl,'.')- All the best Keith

Hi Use a UNION (or a UNION ALL) SELECT * FROM table1 WHERE table1.name = 'John' UNION SELECT * FROM table2 WHERE table2.name = 'John' All the best Keith

Hi YearWeek accepts a 2nd optional parameter to define whether Sunday or Monday is the first day of the week (explained in the week function). http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_week All the best Keith

Hi Think you can do it far more easily than that. Select all the records where the year and week match the current year and week. SELECT vtp_members.id, vtp_members.name, vtp_members.email, count(vtp_tracking.id) surfs FROM vtp_members, vtp_tracking ON vtp_tracking.credit_members_id=vtp_members.id WHERE YEARWEEK(vtp_tracking.action_date = YEARWEEK(CURDATE()) GROUP BY vtp_members.id ORDER BY surfs DESC All the best Keith

Hi If you are doing it as a CRON job, what date range are you looking for? For example, are you looking for any date which is the same week as the job is running in? All the best Keith

Hi You can check a date range using BETWEEN. All the best Keith

Hi Check is a reserved word, so either change the column name (my preferred solution) or surround it with back tics. SELECT * FROM rating WHERE `check`='checked' ORDER BY rating_id DESC LIMIT 3 All the best Keith

Hi Should be able to use substr / left and instr. SELECT LEFT(somefield, INSTR(somefield,'More Useful Tips') -1) FROM sometable All the best Keith

Hi Firstly, set up a table with 1 column 10 rows, with values from 0 to 9. In this case call it integers and the column name of i. Then if you do the following you can select a range of numbers (joining the table against itself repeatedly). This gives you every number from 0 to 999, and the HAVING clause reduces it to 0 to 365. SELECT a.i *100 + b.i *10 + c.i AS SomeNumber FROM integers a, integers b, integers c HAVING SomeNumber <=365 You can then add that number of days to a start date to get a range of dates. For example:- SELECT DATE_SUB( '2011-01-01', INTERVAL a.SomeNumber DAY ) FROM (SELECT a.i *100 + b.i *10 + c.i AS SomeNumber FROM integers a, integers b, integers c HAVING SomeNumber <=365)a All the best Keith