kickstart

Hi Try the SQL directly against the db. And have you changed your column name and the SQL to match with the name of that date column? All the best Keith

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

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

Hi Glad it helped It is a nice break from trying to beat my head against SQL Server and ASP .NET . All the best Keith

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

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

Hi What did they fail with? What is the table layouts? What format is the column user_online.last_activity in? All the best Keith

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

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

Hi Think that is the default behavior for NULL values, which suggests that the columns are blank rather than NULL. All the best Keith

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

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

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

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

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

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

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

Hi Looks like an issue with the precedence of AND and OR. Add brackets around the ORed items (Users_Friends.FriendID = '$user_ID' AND Users_Friends.Level = '1' ) OR (Users_Friends.UserID = '$user_ID' AND Users_Friends.Level = '1' ) All the best Keith

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

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

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

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

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

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

Hi Not sure then as that should work. Although you appear to have an extra $ sign when you try and extract the field from mysql (ie, you want the column eventdate rather than $eventdate), but I would expect that to stop your echo from working as well. All the best Keith