kickstart

Hi No need with an UPDATE to use 2 arrays, just use a single one. <?php $OutArray = array(); $true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC"); while ($true_row = mssql_fetch_assoc($true_query)) { $OutArray[] = $true_row['ID']."='".$_POST['eth_' . $true_row['ID']]."'"; } $sql2 = "UPDATE new_database SET ".implode(",",$OutArray).", DateUpdated='$currenttime' WHERE UserID='$user_ID'"; $insert_result = mssql_query($sql2); ?> However you appears to have a column per check box. All the best Keith

Hi That is close. What that will do is give you every 120th record, starting from the first record. That is basically what the code I provided is doing, except mine also gets the record before the next 120th record in a column. The % is modulus, ie, the remainder after division. So 121 % 120 is 1. All the best Keith

Hi Ah, right. Something like this:- $result = mysql_query("SELECT stringid, MAX(entryid) FROM allmsgs WHERE reported = 'n' GROUP BY stringid") or die (mysql_error()); That should bring the latest entry id for each string id. If you want more details then you can use a subselect of the above joined back against the table. SELECT entryid, stringid, messid FROM (SELECT stringid, MAX(entryid) AS MaxEntryId FROM allmsgs WHERE reported = 'n' GROUP BY stringid) Sub1 INNER JOIN allmsgs ON Sub1.MaxEntryId = allmsgs.entryid AND Sub1.stringid = allmsgs.entryid ORDER BY messid ASC All the best Keith

Hi Probably could. I was trying to come up with SQL that would do it in one go without more SQL or dealing with multiple rows to get the other end of the range (ie using MOD 120 on the row number or the row number -1 would give 2 rows to process for each set). The SQL I knocked up could be pretty much executed and the results output directly. Which is possibly a bit OTT. All the best Keith

Hi Not quite sure what you mean (you seem to be wanting to find the records with a stringid of 5 or 6 so that you can find their stringid). I assume you want to know the entry ids for records with a stringid of 5 or 6 SELECT entryid FROM SomeTable WHERE stringid IN (5,6) However I might have misunderstood things. All the best Keith

Hi Following is one way to do it in SQL:- set @numa = -1; set @numb = -1; SELECT a.customer_name AS StartRangeName, b.customer_name AS EndRangeName, a.customer_index AS StartRangeIndex, b.customer_index AS EndRangeIndex, a.PostedSequence, b.PostedSequence, a.PostedSequence DIV 120 FROM (SELECT customer_index, customer_name, customer_timestamp, @numa := @numa + 1 AS PostedSequence FROM Customers ORDER BY customer_name) a INNER JOIN (SELECT customer_index, customer_name, customer_timestamp, @numb := @numb + 1 AS PostedSequence FROM Customers ORDER BY customer_name) b ON a.PostedSequence DIV 120 = b.PostedSequence DIV 120 WHERE a.PostedSequence % 5 = 0 AND b.PostedSequence % 120 = 119 Down side of this is that it ignores the last page unless it has exactly 120 items on it. Coping with the last page:- set @numa = -1; set @numb = -1; set @numc = -1; SELECT Sub2.PageNo, Sub3.customer_name AS StartRangeName, Sub3.customer_index AS StartRangeIndex, Sub4.customer_name AS EndRangeName, Sub4.customer_index AS EndRangeIndex FROM (SELECT PageNo, MAX(PostedSequence) AS MaxPostedSequence, MIN(PostedSequence) AS MinPostedSequence FROM (SELECT customer_index, customer_name, customer_timestamp, @numa := @numa + 1 AS PostedSequence, (@numa DIV 120) AS PageNo FROM Customers ORDER BY customer_name) Sub1 GROUP BY PageNo) Sub2 INNER JOIN (SELECT customer_index, customer_name, customer_timestamp, @numb := @numb + 1 AS PostedSequence FROM Customers ORDER BY customer_name) Sub3 ON Sub3.PostedSequence = Sub2.MinPostedSequence INNER JOIN (SELECT customer_index, customer_name, customer_timestamp, @numc := @numc + 1 AS PostedSequence FROM Customers ORDER BY customer_name) Sub4 ON Sub4.PostedSequence = Sub2.MaxPostedSequence ORDER BY PageNo All the best Keith

Hi Think you will need to do a JOIN back from a subselect which gets the max and min prices. Something like this:- SELECT Sub1.name, Sub1.MaxPrice, Sub1.MinPrice, (Sub1.MaxPrice -Sub1.MinPrice) AS PriceDiff FROM (SELECT name, DATE(entry_date) AS EntryJustDate, MAX(price) AS MaxPrice, MIN(price) AS MinPrice FROM products GROUP BY name, DATE(entry_date)) Sub1 INNER JOIN products products1 ON Sub1.name = products1.name AND Sub1.EntryJustDate = DATE(products1.entry_date) AND sub1.MaxPrice = products1.price INNER JOIN products products2 ON Sub1.name = products2.name AND Sub1.EntryJustDate = DATE(products2.entry_date) AND sub1.MinPrice = products2.price WHERE products1.entry_date > products2.entry_date AND Sub1.EntryJustDate = DATE(NOW()) Note that isn't tested. Also not that it will bring back some duplicate values if the max or min prices are not unique on a day (can be coded out but adds more complexity and not sure if this is an issue for you). All the best Keith

Hi Bit more of a play. I have briefly tested this and it appears OK, but not 100% sure. This is using a subselect to get a list of all the approved friends, irrespective of whether they are the adder or adde. It then joins the result of that against another subselect. That subselect is using a variable to provide a sequence number of each persons posts in descending order (the set statements at the top initialise the variables required for this). The WHERE clause then narrows it dow to the person whose friends you are interested in and that it is the 2nd entry you care about (or 3rd, or 4th, etc). set @Person = ''; set @num = 1; SELECT * FROM (SELECT adder AS Person, addee AS Friend FROM FriendsTable WHERE approved = 'yes' UNION SELECT addee AS Person, adder AS Friend FROM FriendsTable WHERE approved = 'yes') PersonSubselect INNER JOIN (SELECT postedby, id, @num := if(@Person = postedby, @num + 1, 1) AS PostedSequence, @Person := postedby AS dummy FROM links ORDER BY id DESC) LinksSubselect ON PersonSubselect.Friend = LinksSubselect.postedby WHERE PersonSubselect.Person = 'jack' AND LinksSubselect.PostedSequence = 2 All the best Keith

Hi Brief play. Something like this will get you the latest posts by others you are friends with (not tested so excuse any typos):- SELECT * FROM (SELECT adder AS Person, addee AS Friend FROM FriendsTable WHERE approved = 'yes' UNION SELECT addee AS Person, adder AS Friend FROM FriendsTable WHERE approved = 'yes') PersonSubselect INNER JOIN links ON PersonSubselect.Friend = links.postedby INNER JOIN (SELECT postedby, MAX(id) AS MaxId FROM links GROUP BY postedby) LinksSubselect ON links.postedby = LinksSubselect.postedby AND links.id = LinksSubselect.MaxId WHERE PersonSubselect.Person = 'jack' Finding the 2nd highest, etc, is a bit more difficult. All the best Keith

Hi Not quite sure what you are asking. Cleaning up your code to set up a variable with the SQL insert statement in it you can have this:- $FieldArray = array(); $ValueArray = array(); $true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC"); while ($true_row = mssql_fetch_assoc($true_query)) { $FieldArray[] = $true_row['ID']; $ValueArray[] = $_POST['eth_' . $true_row['ID']]; } $sql = "INSERT INTO new_database (UserId,".implode(',',$FieldArray)."DateUpdated) VALUES ('$user_ID', ".implode("','",$ValueArray).",'$currenttime')"; However I am a but confused as to what it is you really want. Do you really have multiple columns, with one for each of the different check boxes? Normally would be better to have a table with one row per check box and user. All the best Keith

Hi Good spot PFMaBiSmAd Which suggests that one of the tables has multiple matching rows. Ie, if there are 2 orders for a single printer sale record that will bring back 2 rows. All the best Keith

Ie, something like this:- $query = "SELECT * FROM $day,sip_data WHERE sip_data.type='bar' AND $day.id=sip_data.id AND $day.$day$time <=> $day.$day$time1 ORDER BY zip ASC"; echo "$query <br />"; So we know what it has given after substituting the variables for their values. The idea of having multiple tables for different days, and columns split down similarly is a bit strange. All the best Keith

Hi Probably my fault. select members.*, module_forums_posts.*, CASE module_forums_post.last_edit_id WHEN 0 then member.display_name ELSE member2.display_name END as e_display_name from members INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id All the best Keith

Hi What confuses me is that you appear to be passing in a parameter as one of the table names AND the part of the field names (and worryingly, as a field from an input form). First thing I would suggest is to echo out the SQL before you execute it. Could you also post the table layouts and some sample data? If so I can try and have a play. Part of the problem might be that you are using <=> . This will equate to true if both items are NULL. But for that to cause an issue I would expect there to still need to be a records on the table with a matching id. All the best Keith

Hi Looks like if you miss out that line you will land up cross joining table p against the results of Joining o and pr. Possibly more obvious with the query laid out as:- SELECT p.orderid, p.updated, p.origsalesman, p.salesman, p.status, p.profit, p.product, p.orderdate, o.price FROM printers pr INNER JOIN orderdetail o ON o.productid = pr.productid INNER JOIN printersales p ON p.orderid = o.orderid WHERE p.updated > '2011-08-5 00:00:01' All the best Keith

Hi I take it this is for an Ajax call? You have a field where they type the name in and for each letter it does an Ajax call to bring back a list of possible matches? If so the first thing I would suggest is to put in a short delay between people typing a letter and it doing the call back to the server. Doesn't need to be a long delay, but 1/4 of a second will stop repeated calls when someone is typing quickly. Ie, when they type a character you set up a Javascript timer to call a function for the ajax call. The first thing that function does is check if the input field contents are the same as they were when the timer was set up, and if not it abandons the Ajax call. Once you do the call and check the database, 1000 rows isn't much to check and shouldn't take that long. All the best Keith

Alias, as in where you give a column or subselect a different name. Highlighted in red below select members.*, module_forums_posts.*, CASE module_forums_post.last_edit_id WHEN 0 then member.display_name as e_display_name ELSE member2.display_name as e_display_name END from members INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id You need to do this after the END of the CASE statement, rather than once per clause in the CASE statement. All the best Keith

Hi Which suggests that there are no records that match those causes. If you can post the table layouts and sample data then we can try and do some useful checking. Without that we are just guessing All the best Keith

Hi Can't see how it can bring back the wrong emails, except for just bringing back a random 5 because the total is null. Export your table declares and sample data then I can at least try out the code I suggest. All the best Keith

Hi In which case use a subselect to get one:- SELECT a.email, SUM(b.total_amount) + SUM(c.total_amount) FROM (SELECT DISTINCT email FROM cashsumtotals UNION SELECT DISTINCT email FROM cardsumtotals ) AS a LEFT OUTER JOIN cashsumtotals b ON a.email = b.email AND b.cause= '$causename' LEFT OUTER JOIN cardsumtotals c ON a.email = c.email AND c.cause = '$cause_id_get' GROUP BY a.email ORDER BY SUM(b.total_amount) + SUM(c.total_amount) DESC LIMIT 5 All the best Keith

Hi Several errors. First error is that you have put ('2011/01/01', 'yyyy/mm/dd') AND ('2011/12/31', 'yyyy/mm/dd'), which is meaningless. It will probably work with just the dates specified without trying to put the format, or if you had used an odd format then you would need to convert it to a date. Further you appear to be trying to order by the complete sales table rather than any columns. Also you have used a GROUP BY on a field that you do not appear to be returning, while ignoring all the non aggregate fields that you are returning. To get a response for all of a list of products, then assuming that the products are in an array you could use AND sales.productID IN (".explode(',',$productsArray.") Without testing here is a brief attempt at what I think you want SELECT sales.employeeID, sales.leadID, leads.leadID, leads.count, employees.employeeID, employees.firstName, employees.lastName, SUM(sales.refunded) as refund_total, COUNT(sales.refunded) as refunds, SUM(sales.payment) as net, COUNT(sales.payment) as sales, COUNT(sales.purchased) as total_sales FROM sales INNER JOIN employees ON employees.employeeID = sales.employeeID INNER JOIN leads ON leads.leadID = sales.leadID WHERE sales.purchased BETWEEN 2011/01/01' AND '2011/12/31' AND sales.productID IN(1, 2) GROUP BY sales.employeeID, sales.leadID, leads.leadID, leads.count, employees.employeeID, employees.firstName, employees.lastName, ORDER BY employees.lastName, employees.firstName All the best Keith

Hi I would use a case statement to select which column to get the data from. You will also need to change the join on the members table against the edit id to a LEFT OUTER JOIN (otherwise if last_edit_id is 0 there would seem to be no matching member record, and with an INNER JOIN no row would be returned). select members.*, module_forums_posts.*, CASE module_forums_post.last_edit_id WHEN 0 then member.display_name as e_display_name ELSE member2.display_name as e_display_name END from members INNER JOIN module_forums_posts ON module_forums_posts.author_id = members.member_id LEFT OUTER JOIN members as member2 ON module_forums_posts.last_edit_id = member2.member_id All the best Keith

Hi Separate them with full stops. You might as well remove all the back tics as well (only needed when the column names are reserved words, and best to avoid that anyway) All the best Keith

Hi Max shouldn't have 2 columns in it. All the best Keith

Hi Spotted the problem with my SQL. SELECT s.id, s.shop_name, s.payment_plan, ss.add_date, sss.add_time FROM shops s LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id LEFT JOIN ( SELECT ss.shop_id, add_date, MAX(add_time) AS add_time FROM search_up ss GROUP BY shop_id, add_date ) sss ON s.id = sss.shop_id AND ss.add_date = sss.add_date WHERE s.shop_region = 'kansai' AND s.publication = 'yes' ORDER BY s.payment_plan ASC, ss.add_date DESC, sss.add_time DESC Add the extra s highlighted in red. Note that this is going to bring back rows for shops which have no matching records on the search_up table. If you don't want these then change them from LEFT JOINs to OUTER JOINs All the best Keith