kickstart

Hi Using strip slashes like that would be very unsafe. All it takes is for someone to pass a value of something like yes');drop table blah; (bit more than that, but not much) and you would execute a very nasty command. If you want to use regions like that then I would suggest something like this:- $CityRegions = array('Region 1'=> array('Some City','Another City'), 'Region 2'=> array('Ye Another City','SmallVille')); if (array_key_exists($City,$CityRegions)) { $sql .= "City IN ('".implode("','",$CityRegions[$City])."')"; } else { $sql .= "City = '$City'"; } That would take the city, and if it was a region it would use the list of cities defined for that region in the array, if not just check the city matches the specified one (assuming $City already has mysql_real_escape_string applied to it). It would still be best to redesign things to be more database based, but this should cover it as it is. All the best Keith

Hi Stopping bots is a never ending task. You can try many things. Put in a captcha. Vary field names (to make it more difficult to automate). Put in a question for a human to answer. Limit the response time so that any response within a small time of the page being sent are rejected (on the basis no human could fill the form in that quickly). Block ranges of IP addresses. Take an email address and send an email to confirm the vote. Etc. None of these are likely to be 100% proof way of solving the problem. Although the harder you make it the more likely they will just go an find an easier site to attack and spam. As to validation, integers are easy to check and pretty useless for many attacks. All the best Keith

Hi You appear to be mixing up mysql_.... and mysqli_... functions. What is the error returned? All the best Keith

What advantages do sessions have over global variables? They are clearly defined as to what they are and no need to declare them as global in any function that wants to use them without them being passed. Also storing them in session saves having to worry about manually loading and saving them on each page refresh. All the best Keith

Hi While fully echoing what Fenway and Mchl say in this ancient thread, it can be done even easier than that with the following:- SET @rownum = 0; update idtest SET Id = (@rownum:=@rownum+1) ORDER BY id It really is a horrible idea though and updating the primary key like this will destroy any relationships based on it. All the best Keith

Hi You would use mysql_real_escape_string on the variables which would prevent anyone putting a ' inside the variable to take control. However you NEED to have the quotes in the variable (which you are accepting from the user) hence can't block them with mysql_real_escape_string. And so land up leaving yourself open to sql injection. You code already has mysql_real_escape_string in it, in the code you passed earlier. You have looped through $_POST and escaped all the variables and put them into the $param array. All the best Keith

Hi Are team_name and surfs set up as constants? If not then you need quotes around them when using them as indexes to the $row array. Also you are not joining the teams table to the others. It will just do a cross join, bringing back a silly number of records You should also GROUP BY all non aggregate columns. Something like this is needed (based on a guess of joining vtp_members and team on team_id and id). <?php $sql_ranks = ("SELECT vtp_members.id, vtp_members.name, vtp_members.teamleader, teams.team_name, count(vtp_tracking.id) surfs FROM vtp_members INNER JOIN vtp_tracking ON vtp_tracking.credit_members_id = vtp_members.id INNER JOIN teams ON vtp_members.team_id = teams.id WHERE vtp_members.team_id=".$_GET['t']." AND vtp_tracking.action_date > '$last_sunday' AND vtp_tracking.action_date < '$next_sunday' GROUP BY vtp_members.id, vtp_members.name, vtp_members.teamleader, teams.team_name ORDER BY surfs DESC"); $rsranks = mysql_query($sql_ranks); echo "<br><table align='center' valign='top' border='0' width='300px'> <tr><td colspan='2' align='center'><font size='2px'><b>Team Rankings (Current Week)</b></font></td></tr> <tr><td><font size='2px'><b>Team</font></td><td align='right'><font size='2px'>Total Surfs</font></td></tr>"; while ($row = mysql_fetch_array($rsranks)) { echo "<tr><td><font size='2px'><b>".$row['team_name']."</font></td><td align='right'><font size='2px'>".$row['surfs']."</font></td></tr>"; } echo "</table>"; ?> All the best Keith

Hi You are doing the ordering prior to the UNION, rather than on the results of the UNION. All the best Keith

Hi If you must use 2 sets of tables (and I would agree it is best to avoid it), then use UNION. Something like this (not tested so excuse any typos) SELECT post_id, content, added, username, email, RealAnonymous FROM (SELECT post_id, content, added, username, email, 'Anonymous' AS RealAnonymous FROM posts a INNER JOIN anonymous_to_posts b ON a.post_id = b.post_id INNER JOIN anonymous c ON b.anonymous_id = c.anonymous_id UNION SELECT post_id, content, added, username, email, 'Real' AS RealAnonymous FROM posts z INNER JOIN users_to_posts y ON z.post_id = y.post_id INNER JOIN users x ON y.user_id = x.user_id) SomeAliasName ORDER BY added All the best Keith

Hi Something like this should do it SELECT MAX(C2) FROM (SELECT C2 FROM Table1 WHERE C1 = 'Bob' UNION SELECT C2 FROM Table2 WHERE C1 = 'Bob') AliasName All the best Keith

Hi Trouble with that is you are making it VERY easy for anyone to try SQL injection. To avoid delimiting the inverted commas you would need to avoid using mysql_real_escape_string, but that would be pretty dodgy. All the best Keith

Hi Couple of ways to catch no records found. You can set a flag before the select and reset it within the loop. So you know if it has gone into the loop and can put out a message based on that. You can check the number of records returned before the loop. Or you can initially use an if on the mysql_fetch_array, something like this:- <?php $query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id FROM ActualCarsTable INNER JOIN CarMakeTable ON ActualCarsTable.Make = CarMakeTable.Id INNER JOIN CarColorTable ON ActualCarsTable.Color = CarColorTable.Id WHERE ActualCarsTable.Price = '$amount'AND CarColorTable.Color = '$color'"; $result = mysql_query($query) or die(mysql_error()); if($row = mysql_fetch_array($result)) { echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['Color']; echo "<br />"; while($row = mysql_fetch_array($result)) { echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['Color']; echo "<br />"; } } else { echo "No results found<br />"; } ?> All the best Keith

Hi Sorry, that was my fault. I spotted the error and instead made one of my own. Your code is currently $query= "insert into table name('fname','lname') values('$v_fname','$v_lname')"; Either change that to $query= "insert into table name(`fname`,`lname`) values('$v_fname','$v_lname')"; or to $query= "insert into table name(fname,lname) values('$v_fname','$v_lname')"; Back tics (normally at the top left of the keyboard below the escape key - NOT single quotes) are used if a field name is a reserved word. So if you had a field called from you can access it without confusing the SQL. Personally I try to avoid any fields that are reserved words so virtually never need to use them. In your code fname and lname are not reserved words so do not require back ticks, but no technical reason you can't use them. All the best Keith

Hi Mysql_real_escape_string is used to prevent some potentially dangerous things being added. For example if you allow a single quote then that could signal the end of the string you are inserting. Most of the time this would just result in an error, but someone could be devious and put in a single quote followed by enough other info to complete the INSERT followed by a semi colon and then some rather naughty piece of SQL (such as DROP TABLE) which would wreck your site. Name is highly unlikely to be unique for long. Even full name. There are loads of John Smiths around. Only error that jumps out on me is the INSERT. You have:- $query=insert into "database","table"("fname","lname") values("$v_fname","$v_lname",); which is missing various quotes and has a load of extra ones, along with an extra comma. Try $query= "insert into table('fname','lname') values('$v_fname','$v_lname')"; All the best Keith

Hi Not sure what you are asking. You can add a timestamp field to the table to automatically record the insert date / time. Or you can specify a value in a date / time field (NOW() will give you an appropriate value). You should also use mysql_real_escape_string on the values you are inserting into the database to prevent SQL injection attacks. Further your table should have a unique key. Name is unlikely to be unique long term. Probably best to have an Id field which is just an autonumber integer field (ie, each row that is inserted gets the next value). All the best Keith

Hi You are not joining all the tables together. Also, personally best to split the join conditions off into an ON clause rather than just using the WHERE clause. You also have a missing " I think $query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id" . "FROM ActualCarsTable" . "INNER JOIN CarMakeTable ON ActualCarsTable.Make = CarMakeTable.ID" . "INNER JOIN CarColorTable ON ActualCarsTable.Make = CarMakeTable.ID" . "WHERE ActualCarsTable.Price = '$amount'" . "AND CarColorTable.Color = '$color'"; All the best Keith

Hi Ah, right. So you have a category, which contains products which have features, and products contain phones that have features. So in simple terms the category could be a pre pay deal or a pay monthly deal, while a product under that category could be (say) a £30 a month contract which could have a feature of free insurance or a £15 a month contract with no free insurance, while a phone could also have a feature of free insurance. If so something like this:- SELECT DISTINCT cat.id, product.id, phone_id FROM categories cat INNER JOIN products ON cat.id = Products.cat_ids INNER JOIN product_features ON products.id = product_features.product_id INNER JOIN phone ON products.id = phone.product_id INNER JOIN phone_features ON phone.id = phone_features.phone_id WHERE product_features.feature_id IN (somefeature, anotherfeature, etc) OR phone_features.feature_id IN (somefeature, anotherfeature, etc) That gives you a list of all the categories / phoducts / phones where either the product or phone have one of the required features. If you want a list of these which have ALL the required features (either on the product or the category) SELECT DISTINCT cat.id, product.id, phone_id FROM categories cat INNER JOIN products ON cat.id = Products.cat_ids INNER JOIN product_features ON products.id = product_features.product_id INNER JOIN phone ON products.id = phone.product_id INNER JOIN phone_features ON phone.id = phone_features.phone_id WHERE somefeature IN (product_features.feature_id, phone_features.feature_id AND anotherfeature IN (product_features.feature_id, phone_features.feature_id All the best Keith

Hi How does the phone table relate to the products or categories tables? I see that a feature belongs to a product or phone and that a product belongs to a category, but I can't see how a phone relates to a product or category. All the best Keith

Hi The assumption with that query is that it will only bring back any row where there is nothing found on the subselect. Hence if you want to check a value from the subselect to be something in particular it will never bring anything back. However if you want to match against another field then put it in the ON clause. "SELECT a.*, CONCAT('$', low_price) AS low_price FROM inventory a LEFT OUTER JOIN (SELECT item_id, sub_category_b FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b ON a.item_id = b.item_id AND a.sub_category_b = b.sub_category_b WHERE b.item_id IS NULL LIMIT 5" All the best Keith

Hi If all you want is the category id and product id of things that have the features you need then something like this will do it SELECT cat.id, product.id, COUNT(*) FROM categories cat INNER JOIN products ON cat.id = Products.cat_ids INNER JOIN product_features ON products.id = product_features.product_id WHERE product_features.feature_id IN (somefeature, anotherfeature, etc) GROUP BY cat.id, product.id All the best Keith

Hi Taking a step back, we need to know how the columns join up. What fields on each table match those on other tables? How do things relate to each other? All the best Keith

Hi If you don't want anything to happen when the row already exists then you can just use INSERT IGNORE INTO .......... If you want to update the rows when they already exist you can use INSERT.... ON DUPLICATE KEY UPDATE...... However not sure which you want to do. All the best Keith

Hi The SQL above uses LEFT OUTER JOINs which will return an empty matching row if there is no match. For example say you had a table of people and a table of children. If you did a normal JOIN (INNER JOIN) between the 2 tables then anyone on the first table who didn't have any children wouldn't be returned. If you use a LEFT OUTER JOIN then anyone on the first table who had no children would be brought back but with the columns from the children table being NULL. You can exclude the unwanted records in the WHERE clause (or if you want in the ON clauses). All the best Keith

Hi You can use the ON DUPLICATE KEY type syntax to cope with it (saves you doing the SELECT first). However what do you want to do with the records should it already be there? All the best Keith

Hi This isn't really a MySQL question. 2 basic ways to do this. Either get all the data required when you first produce the menu (no need to display it, could use Javascipt to display it on a popup). Or you bring the data back when a user clicks on the link. In this case you can either go to a fresh page to display the data, or you can do an AJAX call to retrieve the data and display it on screen. All the best Keith