Jump to content

kickstart

Staff Alumni
 View Profile  See their activity

  • Posts

    2,707

  • Joined

  • Last visited

 Content Type 

Posts posted by kickstart

    PHP While loop inside SQL Insert

    in MySQL Help

    Posted

    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

    Selecting entries with same value in a field

    in MySQL Help

    Posted

    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

    Finding every Nth record in a database with a non-incremental index.

    in MySQL Help

    Posted

    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

    Selecting entries with same value in a field

    in MySQL Help

    Posted

    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

    Finding every Nth record in a database with a non-incremental index.

    in MySQL Help

    Posted

    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

    MYSQL Time difference between MAX and MIN from single table

    in MySQL Help

    Posted

    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

    DB networking with indefinite variables

    in MySQL Help

    Posted

    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

    DB networking with indefinite variables

    in MySQL Help

    Posted

    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

    PHP While loop inside SQL Insert

    in MySQL Help

    Posted

    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

    Something wrong with my query

    in MySQL Help

    Posted

    Hi

     

    Good spot PFMaBiSmAd

     

    this query gives me 26 result and when I add few more fields such as printername and the price which are taken from different tables it goes all wrong

     

    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

    Query returns rows that are empty??

    in MySQL Help

    Posted

    That sounds like the case, indeed, for these reasons:

    [*]I have empty rows throughout the DB (since some corresponding times do not have anything valuable to use in the way of data).

    [*]The query is essentially saying match the id from the $day.$day$time table to the corresponding id on the sip_data table.

     

    I would avoid have null rows. However even a null row shouldn't match unless the both the $day.$day$time and $day.$day$time1 columns are null

     

    I've tried changing up the query a bit, tried a BETWEEN in place of AND, all to no avail.

     

    When you say "echo out the SQL", what do you mean exactly?

     

    Thanks for the help.

     

    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

    Question about joining 2 tables.

    in MySQL Help

    Posted

    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

    Query returns rows that are empty??

    in MySQL Help

    Posted

    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

    Something wrong with my query

    in MySQL Help

    Posted

    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

    select distinct help pls

    in MySQL Help

    Posted

    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

    Question about joining 2 tables.

    in MySQL Help

    Posted

    i am not sure what you mean by 'alias' ?

     

    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

    Combining 2 tables and outputting top 5 results

    in MySQL Help

    Posted

    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

    Beyond Frustrated!!!

    in MySQL Help

    Posted

    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

    Question about joining 2 tables.

    in MySQL Help

    Posted

    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

    Max() Error, Why?

    in MySQL Help

    Posted

    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

    MySQL sorting problem

    in MySQL Help

    Posted

    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

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.