kickstart

Hi One possibility would be to design several tables to store the data more efficiently and then create a view joining those tables to get the 500 columns required by the existing program. All the best Keith

Hi The problem is that you have the HAVING clause in the wrong place. If should be after the GROUP BY. However you shouldn't have non aggregate columns in the SELECT clause which are not in the GROUP BY clause. MySQL is pretty tolerant of this, but some flavours of SQL will reject it out of hand. All the best Keith

Hi If the data is there your SQL should provide what you want. You you post the data on here. However I am not sure you are joining on the correct columns. You are joining on l.id = c.id, just to return c.id as the category id when you could just return l.id and not do the join. All the best Keith

Hi Or without using union but instead joining on both and using IF in the columns selected to determine which tables columns to bring back SELECT CID, CSID, SCode, LName, IName, if(SA_Subscriptions.CCat = 0,SA_Prices1.PCash,SA_Prices2.PCash), if(SA_Subscriptions.CCat = 0,SA_Prices1.PDirect,SA_Prices2.PDirect), CStart, CEnd FROM SA_Subscriptions LEFT JOIN SA_Sessions ON SA_Sessions.SUID = SA_Subscriptions.CSID LEFT JOIN SA_Lessons ON SA_Lessons.LUID = SA_Sessions.SLesson LEFT JOIN SA_Instructors ON SA_Instructors.IUID = SA_Sessions.SInstructor LEFT JOIN SA_Prices SA_Prices1 ON SA_Prices.PRef = SA_Lessons.LPRate1 LEFT JOIN SA_Prices SA_Prices2 ON SA_Prices.PRef = SA_Lessons.LPRate2 WHERE CUID = 2927 AND (SA_Subscriptions.CEnd >= CURDATE() OR SA_Subscriptions.CEnd = '0000-00-00') AND (SA_Prices.PEnd >= SA_Subscriptions.CStart AND SA_Prices.PStart <= SA_Subscriptions.CStart) All the best Keith

Hi Never tried such syntax, although it would make sense, and don't think it is supported in mysql. Equivalent can be done using UNION SELECT CID, CSID, SCode, LName, IName, PCash, PDirect, CStart, CEnd FROM SA_Subscriptions LEFT JOIN SA_Sessions ON SA_Sessions.SUID = SA_Subscriptions.CSID LEFT JOIN SA_Lessons ON SA_Lessons.LUID = SA_Sessions.SLesson LEFT JOIN SA_Instructors ON SA_Instructors.IUID = SA_Sessions.SInstructor LEFT JOIN SA_Prices ON SA_Prices.PRef = SA_Lessons.LPRate1 WHERE CUID = 2927 AND SA_Subscriptions.CCat = 0 AND (SA_Subscriptions.CEnd >= CURDATE() OR SA_Subscriptions.CEnd = '0000-00-00') AND (SA_Prices.PEnd >= SA_Subscriptions.CStart AND SA_Prices.PStart <= SA_Subscriptions.CStart) UNION SELECT CID, CSID, SCode, LName, IName, PCash, PDirect, CStart, CEnd FROM SA_Subscriptions LEFT JOIN SA_Sessions ON SA_Sessions.SUID = SA_Subscriptions.CSID LEFT JOIN SA_Lessons ON SA_Lessons.LUID = SA_Sessions.SLesson LEFT JOIN SA_Instructors ON SA_Instructors.IUID = SA_Sessions.SInstructor LEFT JOIN SA_Prices ON SA_Prices.PRef = SA_Lessons.LPRate2 WHERE CUID = 2927 AND SA_Subscriptions.CCat != 0 AND (SA_Subscriptions.CEnd >= CURDATE() OR SA_Subscriptions.CEnd = '0000-00-00') AND (SA_Prices.PEnd >= SA_Subscriptions.CStart AND SA_Prices.PStart <= SA_Subscriptions.CStart) All the best Keith

Hi Would be best to run some kind of extract regularly. Loop through the files in the directory and process any new ones. Extract the data from the new ones and put it into a suitable set of database tables. If the columns are consistent then you can probably import them easily. However if some of the columns have a limited number of values then you might want a table of those values and insert on the main table just a pointer to the appropriate value (ie, if a column was for car make, have a table of makes with a numeric ID field for each, with your main table just storing the numeric id). There are front ends for MySQL (ie, phpmyadmin) which allow access to the tables and to run SQL but they are not designed for user use and I would advise against opening the tables up that way. It would probably be best to code the extracts that people want so they can just click a button to get the data they want. All the best Keith

Hi Certainly possible and not that difficult. Do the files you read change or are they static once uploaded? Rereading hundreds of files each time the page is accessed to see which have changed would be time consuming. How long it will take will depend on how flexible the solution needs to be. Do the number of columns on the input files change over time? Do you need to cope with these changes without any code changes? All the best Keith

Hi Not sure it is noticeably faster to use AVG, but it is a bit more readable. SELECT b.id, b.name, COUNT( * ) AS ratings, avg(( managers + difficulty + fun + hours + pay + flexibility ) / 6) AS total FROM ratings r LEFT JOIN businesses b ON ( r.business_id = b.id ) WHERE r.status = 2 GROUP BY b.id, b.name ORDER BY total DESC LIMIT 10 All the best Keith

Hi I have a small web site I have got running, on a Windows server mainly using Access databases ( :'( ). This is working fine as it is now. I have added some more processing which entails connecting to a MySQL database and I have this working fine on my development machine. Came to put it on the server and the page cannot be found. I have traced it down to the line I have that follows:- $conn = mysql_connect($dbhost,$dbuser,$dbpasswd); If I comment that line out (and don't execute any MySQL) then the page loads fine. Leave that line in and I get a 500 error. MySQL appears to be running and if I use MSQL Workbench with the same connection details it connects and lets me see the tables. Any ideas? All the best Keith

Hi Need to know what ibfk_7 is. What I meant by splitting them onto a separate table is you would have something like:-- Table of industries. Industries Id, Name 1, Food 2, Electrics 3, Spannering Table of people People Id, Name, etc 1, Jo Bloggs, etc 2, Joe Bloggs, etc 3, Bilbo Baggins, etc Then you have a table of preferred industries PrefInd Id, IndId, PeopleId 1, 1, 1 2, 1, 2 3, 2, 2 4, 2, 3 5, 3, 1 6, 3, 3 This way on the last table IndId refers to the Id field on the industries table while PeopleId refers to the Id on the People table. All the best Keith

Hi You do have the parent / child thing right I think. However it seems that the cascade doesn't work if you have 2 columns on a child table both referring to the same parent (that is what the quote I posted seems to say). Try taking the "on update cascade" out for now and see if it fixes it. If it does then I think what you will need to do is split those 2 columns off to instead be a pair of rows on a further table (this would be better design anyway, and would allow you any number of preferred industries per person in the future). All the best Keith

Hi Not sure the cascade should happen (I assume you don't want the value of a selected industry to cascade to the industries table, and not sure of any others you would want to update). However from the mysql documentation:- http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html Looks like cascade wouldn't appreciate the 2 columns on the users table which both related to the same column of the categories table. All the best Keith

Hi No need to split the array and then do a for loop. You can just use foreach <?php if (count($diff_info) > 0) { $db_update = ''; foreach($diff_info AS $diff_key=>$diff_value) { $db_update .= ", $diff_key = '" . mysql_real_escape_string($diff_value) . "' "; } $query = "UPDATE db_table SET field_one = '$value_one' " . $db_update . " WHERE field_some = '$value_some'"; if (mysql_query($query)) { return true; } else { return false; } } return false; // nothing to update! ?> Something like that should do it All the best Keith

Hi You appear to be wanting to get the foreign key from one table which refers to the primary key in other tables where you haven't yet inserted the rows. I think you need to reverse the order of your inserts and use mysql_insert_id to get the last inserted key fields. All the best Keith

Hi What are the selects for in each insert? Also I presume the id fields are numeric fields. Set them to NULL rather than ''. All the best Keith

Hi You appear to be trying to insert to multiple tables (with the WHERE clause seeming to be for a JOIN) with a single insert statement. Don't beleive that MySQL supports this kind of statement. All the best Keith

Hi The outer select seems redundant, but I presume when you really use it you will join the results of the sub select with another tables data. You appear to be manually calculating the average rather than using the AVG function. You probably want an index on ratings.business_id Hopefully someone can help with something more useful than these points though. All the best Keith

Hi Personally I would have the select lists containing the items but with values of the ID fields of the item. For example, say you had a table of makes:- CarMakeTable Id, Make 1, Alfa Romeo 2, Audi 3, Bentley And a table of actual cars ActualCarsTable Id, Make, Price 1, 1, 10000 2, 1, 11000 3, 1, 5000 4, 2, 7000 5, 2, 4000 6, 2, 11000 7, 3, 20000 You would have a drop down list of (say):- <select name="make"> <option value="1">Alfa Romeo</option> <option value="2">Audi</option> <option value="3">Bentley</option> </select> Then to get all the details for a selected make:- $sql = "SELECT a.Id, b.Make, a.Price FROM ActualCarsTable a INNER JOIN CarMakeTable b ON a.Make = b.Id WHERE b.Id = ".intval($_REQUEST['make']; All the best Keith

Hi Probably best to do it with a JOIN Something like this SELECT a.user, COUNT(b.killed) FROM rsca2_players a LEFT OUTER JOIN rsca2_kills b ON a.user = b.user GROUP BY a.user All the best Keith

Hi For a unique key I would just add an INT auto increment primary key to the table. If you add this it should auto populate. All the best Keith

Depends which of the wheres are deleted . All the best Keith

Hi For now try $result=mysql_query($query) or die(mysql_error()); //runs the posted query (NO PROTECTION FROM INJECTION HERE) All the best Keith

Hi Can you echo out the SQL that is failing and paste it here? All the best Keith

Hi You appear to have 2 separate where clauses. All the best Keith

Hi You appear to be trying to insert nothing into `cellphone;` rather than NULL. All the best Keith