Jump to content

kickstart

Staff Alumni
 View Profile  See their activity

  • Posts

    2,707

  • Joined

  • Last visited

 Content Type 

Posts posted by kickstart

    Complex (for me) MySQL Query - Driving me nuts!

    in MySQL Help

    Posted

    Hi

     

    The %s in the string is a place holder, and will be replaced by the 2nd, 3rd, 4th, etc arguments to sprintf. However you are only supplying one.

     

    Also don't know what the values of $sort or $range are.

     

    You are also not cleaning the input at all. So are wide open to SQL injection (depending on what GetSQLValueString does).

     

    Something like this is wanted (assuming $sort is sanitised before use):-

     

    $query_sellsearch = sprintf("SELECT * FROM selltable WHERE ((`btitle` LIKE '%1$s') OR (`bisbn` LIKE '%2$s') OR (`bauthor` LIKE '%3$s') OR (`bpubyear` LIKE '%4$s') OR (`bpublisher` LIKE '%5$s')) AND `archive`=0 ORDER BY %6$s LIMIT %7$d, %8$d ", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", $sort, $startRow_sellsearch, $maxRows_sellsearch);

     

    All the best

     

    Keith

    Query last saturday to this friday

    in MySQL Help

    Posted

    Hi

     

    Firstly, avoid using a column name like "Date". Anything that is or may be a reserved word in the future is just a pain.

     

    The MySQL week function only supports changing the start of the week between Sunday and Monday I think, which is a bit of a pain.

     

    Using SQL to determine the week (and changing the column name to aDate)

     

    <?php
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT *
FROM events
INNER JOIN (
SELECT DATE(DATE_ADD(NOW(), INTERVAL(CASE DAYOFWEEK(NOW()) WHEN 1 THEN -1 WHEN 2 THEN -2 WHEN 3 THEN -3 WHEN 4 THEN -4 WHEN 5 THEN -5 WHEN 6 THEN -6 ELSE 0 END) DAY)) AS PrevSaturday,
DATE(DATE_ADD(NOW(), INTERVAL(CASE DAYOFWEEK(NOW()) WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 WHEN 4 THEN 2 WHEN 5 THEN 1 WHEN 6 THEN 0 ELSE 6 END) DAY)) AS NextFriday) AS DateRange
WHERE events.aDate BETWEEN DateRange.PrevSaturday AND DateRange.NextFriday";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();
?>

     

    All the best

     

    Keith

    selecting the row after group by

    in MySQL Help

    Posted

    Hi

     

    I am not 100% sure what you are trying to get, as you seem to be saying to filter for something, then your SQL filters them out if that is the case.

     

    However to get the latest item you need a subselect for that to JOIN on as well. Also as you need matching rows on both tables you can use an INNER JOIN rather than an OUTER JOIN.

     

    Something like this, although you will have to check it is really what you want:-

     

    SELECT table1.t1id, table2.time, table2.value 
FROM table1 
INNER JOIN table2 
ON table1.t1id = table2.t1id AND table1.active = true
INNER JOIN (SELECT t1id, MAX(time) AS MaxTime FROM table2 WHERE current = false GROUP BY t1id) SubQuery
ON table2.t1id = SubQuery.t1id AND table2.time = SubQuery.MaxTime

     

    All the best

     

    Keith

    Query with clauses from two tables

    in MySQL Help

    Posted

    Hi

     

    Not quite sure how your tables join up, but try something like this:-

     

    $query1 = "SELECT user_favorites.favorited 
FROM user_favorites
INNER JOIN user_online 
ON user_favorites.favorited = user_online.user
WHERE user_favorites.user='$user' 
AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE)";

     

    I presume what you want is to get the all the favourites of the current user from user_favourites and join that to user_online to find which favourites have been online in the last 15 minutes.

     

    Going through your other attempts to explain what seems to be happening:-

     

    $query1 = "SELECT user_favorites.favorited 
FROM user_favorites 
WHERE (SELECT FROM user_online WHERE user_online.user='user_favorites.favorited' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE))";

     

    Not sure what this will do I would expect a syntax error. The subselect isn't bringing anything back, and even if it does the WHERE clause doesn't compare it with anything.

     

    $query1 = "SELECT user_favorites.favorited, user_online.last_activity 
FROM user_favorites JOIN user_online ON user_online.user=user_favorites.user  
WHERE user_favorites.user='$user' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 40 MINUTE)";

     

    This is doing the join but on the current users field. Effectively bring back just the current user.

     

    $query1 = "SELECT user_favorites.favorited 
FROM user_online, user_favorites 
WHERE user_favorites.user='$user' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 40 MINUTE)";

     

    No fields specified to do the join, hence it will do a cross table join and bring back a hell of a lot of rows.

     

    $query1 = "SELECT user 
FROM user_online 
WHERE last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE) AND (SELECT favorited FROM user_favorites WHERE user = '$user')";

     

    Doesn't actually check against anything from the subselect.

     

    $query1 = "SELECT user_favorites.favorited 
FROM user_favorites, user_online 
WHERE user_favorites.user='$user' 
AND (user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE))";

     

    This is the same as your 3rd attempt.

     

    All the best

     

    Keith

    MySQL Join Help

    in MySQL Help

    Posted

    Hi

     

    Not sure why you are checking for 35.

     

    This:-

     

    SELECT InProcess_ID, Operation_ID, Basic_Dimn, InProcessDimn_ID, IP_ID, Operation_ID, Batch_ID, Job_NO, Dimn_Measured
FROM InProcess
LEFT OUTER JOIN InprocessDimns
ON InProcess.InProcess_ID = InprocessDimns.IP_ID
WHERE InProcess.Operation_Id = 10

     

    will get all the rows, with blank columns where there is no matching record.

     

    With checking the job_no, you want to only do the join on columns which have 35 in that field, rather than doing an outer join then excluding records without 35 in that field:-

     

    SELECT InProcess_ID, Operation_ID, Basic_Dimn, InProcessDimn_ID, IP_ID, Operation_ID, Batch_ID, Job_NO, Dimn_Measured
FROM InProcess
LEFT OUTER JOIN InprocessDimns'
ON InProcess.InProcess_ID = InprocessDimns.IP_ID AND InprocessDimns.Job_NO=35
WHERE InProcess.Operation_Id = 10

     

    All the best

     

    Keith

    Ways of storing data...

    in MySQL Help

    Posted

    Hi

     

    Seems a bit strange to have a table of ID fields (which are basically meaningless) and to never join these to a table of people to get a meaningful value for them (such as name). And if you do get the value you either need to use a join (efficient), do an extra query (inefficient) or do a query per row (hideously inefficient).

     

    All the best

     

    Keith

    CONCAT_WS and CONCAT help

    in MySQL Help

    Posted

    Hi

     

    What is the problem you are having with it?

     

    SELECT CONCAT_WS('-',account.phone_country_code,account.phone_city_code,account.phone) AS phone 
FROM account

     

    Or is it that blank columns are skipped on a row? If so use IFNULL on each column.

     

    All the best

     

    Keith

    mysql query join tables

    in MySQL Help

    Posted

    Hi

     

    That case statement makes no sense there, and also even if coded to make sense is not a nice way of doing things.

     

    I would use a UNION of 2 selects, one to check each user field.

     

    If you find that messy to look at then you can hide the UNION in a view to return each row twice, once for each way around the user fields are.

     

    All the best

     

    Keith

    Question about create dynamic table in mysql!

    in MySQL Help

    Posted

    Hi

     

    Creating / modifying tables in that way is fairly easy. Just use a create statement as you have suggested, but changing the table name.

     

    However from a design point of view it is a really bad idea and will result in a quite horrible database, making it very difficult to extract data from it.

     

    All the best

     

    Keith

    Select from 2 rows ordering by a different Table

    in MySQL Help

    Posted

    Hi

     

    Right, get it now. I thought you wanted them sorted within whether they were users or friends, but you just need them sorted overall:-

     

    SELECT Users_Friends.UserID, Users_Friends.FriendID, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.UserID = Users.ID 
WHERE Users_Friends.FriendID = 1 
AND Users_Friends.Level = '1'
UNION
SELECT Users_Friends.FriendID, Users_Friends.UserID, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.FriendID = Users.ID 
WHERE Users_Friends.UserID = 1 
AND Users_Friends.Level = '1' 
ORDER BY UserName ASC

     

    All the best

     

    Keith

    Select from 2 rows ordering by a different Table

    in MySQL Help

    Posted

    Hi

     

    Can you list  the records going in and coming out, and what order you expect them in.

     

    From your description I am not sure which of your output records is from where on the friends table.

     

    Also check by swapping the fields checked by the 2 where clauses.

     

    (SELECT '1' AS SortCode, Users_Friends.UserID, Users_Friends.FriendID, Users_Friends.DateRequested, Users.UserName

    FROM Users_Friends

    INNER JOIN Users ON Users_Friends.UserID = Users.ID

    WHERE Users_Friends.UserID = '$user_ID'

    AND Users_Friends.Level = '1')

    UNION

    (SELECT '2' AS SortCode, Users_Friends.FriendID, Users_Friends.UserID, Users_Friends.DateRequested, Users.UserName

    FROM Users_Friends

    INNER JOIN Users ON Users_Friends.FriendID = Users.ID

    WHERE Users_Friends.FriendID = '$user_ID'

    AND Users_Friends.Level = '1')

    ORDER BY SortCode, UserName ASC

     

    All the best

     

    Keith

    Select from 2 rows ordering by a different Table

    in MySQL Help

    Posted

    Hi

     

    Getting rather confused over what you want.

     

    It seems that what you want is 2 queries unioned together, so you can sort the values.

     

    Nothing in he code you posted earlier has anything that will sort them except in user name order.

     

    My guess at what you want (and it is a guess, and I might be checking the friend id / user id the wrong way round) is something like this:-

     

    (SELECT '1' AS SortCode, Users_Friends.UserID, Users_Friends.FriendID, Users_Friends.DateRequested, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.UserID = Users.ID 
WHERE Users_Friends.UserID = '$user_ID' 
AND Users_Friends.Level = '1')
UNION
(SELECT '2' AS SortCode, Users_Friends.FriendID, Users_Friends.UserID, Users_Friends.DateRequested, Users.UserName 
FROM Users_Friends 
INNER JOIN Users ON Users_Friends.FriendID = Users.ID 
WHERE Users_Friends.FriendID = '$user_ID' 
AND Users_Friends.Level = '1') 
ORDER BY SortCode, UserName ASC

     

    All the best

     

    Keith

    Query Joining Help

    in MySQL Help

    Posted

    Hi

     

    Only thing I can spot wrong is that you have given alias' to some of the columns brought back by the SQL but then referred to the column names rather than the alias names when outputting them (eg you have used $newsF['post_subject'] rather than $newsF['Title'])

     

    All the best

     

    Keith

    help writing a (somewhat) complex query...

    in MySQL Help

    Posted

    Hi

     

    Sorry, my fault. Should be a column name (missed it from the last one). Add .projid to the table name there, which I seemed to removed in the last edit.

     

    Basic lesson.

     

    Normal SQL from a table is pretty fast, but there is a fair overhead for just doing a query. So doing one query and then looping round doing another query for each row suffers a big overhead.

     

    In a query you can JOIN two or more tables together. Couple of types of JOIN:-

     

    INNER JOIN - this brings back a row where there is a matching column value on each table. Say you had a table of bank accounts and a table of transactions, if one account had no transactions then no row would come back for that account

     

    OUTER JOIN - this will bring back a row when there is no matching row on the other table. If the bank account situation it would bring back a row for the account but the columns that should refer to the transactions would be NULL for any account with no transactions.

     

    CROSS JOIN - this brings back every combination of row. You might use this if you wanted a list of all the possibilities, and then use an outer join onto a 3rd table when that possibility had occured. For example, say you had a list of people and a list of days of the week, plus a table containing days that a particular person had been shopping, you could get a list of all the people and days and a count of how many times (if at all) that person had been shopping on that day.

     

    In your basic query you need an OUTER JOIN. This is giving you a list of all the people, and then joining that with the table of projects people are in. Putting the check for the project in the ON clause means it is only matching on projects you are interested in. So if Fred is involved in project 123 it will match that row, but if not there will be no row matched.

     

    All the best

     

    Keith

    help writing a (somewhat) complex query...

    in MySQL Help

    Posted

    Hi

     

    If you only want a single project at once then you can bypass the CROSS JOIN.

     

    However you need to check the project ID in the ON clause of the OUTER JOIN (not in a WHERE clause).

     

    $sSql = "SELECT users.*, users_projects.projid AS UserProjectFound FROM users LEFT OUTER JOIN users_projects ON users.userid = users_projects.userid and users_projects = 123";
$rs = mysql_query($sSql) or die(mysql_error());

while ($row = mysql_fetch_assoc($rs)) 
{
echo "<input type='checkbox' name='user[]' value='".$row['userid']."' ".(($row['UserProjectFound'] == '') ? '' : "checked='checked'" )." />".$row['userid'];
}

     

    All the best

     

    Keith

    help writing a (somewhat) complex query...

    in MySQL Help

    Posted

    Hi

     

    So you need to join on the user id.

     

    Is this to provide a list of users and then a list of projects, with a tick box for any project which is checked if the user is on it?

     

    If so something like this:-

     

    $sSql = "SELECT users.*, projects.*, users_projects.projid AS UserProjectFound FROM users CROSS JOIN projects LEFT OUTER JOIN users_projects ON users.userid = users_projects.userid and projects.projid = users_projects.projid";
$rs = mysql_query($sSql) or die(mysql_error());

while ($row = mysql_fetch_assoc($rs)) 
{
echo $row['usersname']." - ".$row['projectname']."<input type='checkbox' name='project".$row['projid']."' ".(($row['UserProjectFound'] == '') ? '' : "checked='checked'" )." />";
}

     

    This is doing a cross join between users and projects to get a list of every project for every user (you might want to add an order by clause to make the order more useful), and then a left join to get the details for that user / project.

     

    Idea is that for a user you will get a row returned for every project, and the details if they exist if that user is assigned to that project.

     

    All the best

     

    Keith

    help writing a (somewhat) complex query...

    in MySQL Help

    Posted

    Hi

     

    Looks like you need a LEFT OUTR JOIN.

     

    Something like this (quickly knocked up).

     

    $sSql = "SELECT users.*, users_projects.projid AS UserProjectFound FROM users LEFT OUTER JOIN users_projects ON users.projid = users_projects.projid";
$rs = mysql_query($sSql) or die(mysql_error());

while ($row = mysql_fetch_assoc($rs)) 
{
echo "<input type='checkbox' name='project".$row['projid']."' ".(($row['UserProjectFound'] == '') ? '' : "checked='checked'" )." />";
}

     

    All the best

     

    Keith

    join multiple table

    in MySQL Help

    Posted

    Hi

     

    Tables are joined where the info in them is related in some way. Ie, a table of people with a table of hair colours, with the table of people having a column with an id field giving their hair colour which is a key to the table of hair colours.

     

    It is these relationships that are important and you need to know these before you can join them together.

     

    You can do what is known as a cross join which will get every possible combination. Useful if you have (say) a table of people and a table of days and want a list of people and days which you can then link to other data (such as what they had for breakfast, if they had any). However the number of records will be massive (ie, 2 tables each of 100 rows cross joined together will result in 10000 rows returned). If you 19 tables each had only 10 rows then cross joining them all together would result in 10,000,000,000,000,000,000 being returned!

     

    We need more info on your tables and what you want returned before we can give you any useful advice on what to do.

     

    All the best

     

    Keith

    Inserting into two tables.

    in MySQL Help

    Posted

    Hi

     

    You can't insert into 2 tables at once.

     

    You can insert to the 2nd table, get the last insert id and then use that when inserting into the first table.

     

    If you want to make sure it all works you can use commit / rollback.

     

    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.