mikosiko

if you are adding columns dynamically on you table, as your question seems to imply, then definitively something is very wrong in your design

in the posted code, none of this variables '$name','$email','$hometown','$comments' has been defined... how are you getting their values?

in general is bad idea to mix implicit (or theta) with explicit (or ansi) syntax, your query has evident syntax errors, I suggest you to read deeper in how to write and use the different kind of JOIN's existent , this could be a starting learning resource for you http://en.wikipedia.org/wiki/Join_(SQL) and of course the manual pages for MYSql are always available too http://dev.mysql.com/doc/refman/5.0/en/join.html for now try this o see if you get what you described in your first post: SELECT photos.thumb, product.id_prod, product.title, photos.thumb_width, photos.thumb_height FROM products JOIN members ON (product.id_mem = members.id_mem AND mem_group >=1 AND mem_group <100) LEFT JOIN photos ON (product.id_prod = photos.id_prod AND photos.main_photo = '1') WHERE product.publish = '1' ORDER BY product.id_prod DESC

use explicit JOIN's ... use LEFT JOIN... and is a OR that shouldn't be there... try and post the code back

and what problem do you have?.... I don't see nothing than a LEFT JOIN couldn't solve there

the real gain is mainly because the index that you just added on content_id

test if you get improvement with this changes, on your second table - Drop the index value_index_id ... it is a duplicate from the KEY index - Add an index on the column content_id

by the way... will help a lot if you post your full table descriptions including the indexes for each one

again... depend on your objectives... if your objective is generate a query that return only one row for each content_id, then your query is doing that as an example only (could be not valid for your objectives) try this and think how you can post-process the results and if that fit your goals SELECT c.content_title, c.content_body, c.content_image, c.permalink, cv.content_option_id, cv.content_option_value FROM category_items LEFT JOIN content c ON category_items.content_id = c.content_id LEFT JOIN content_values cv ON c.content_id = cv.content_id WHERE category_items.category_id = '10937' if that doesn't serve you I will let others with more expertize to help you to optimize your current query

if your objective is generate a query that return only one row for each content_id, then your query is doing that, however as soon as you decide to add a new content_value (in case a new business requirement) you will need to modify the query to add the new condition (which to me, seems a bad Db design to start with, but that is a different topic). with the situation/configuration on hand I will probably simplify the query to return the full JOIN'ed record set, and I will post-process the result (using an array maybe) according to what is needed at display time... but that is just me.

As I said... just guessing... without knowing your tables descriptions, some data example and expected results (hence your objectives) is hard to tell

Nothing there is duplicated or "messed" as Dan is trying to explain to you (option 2 in his answer)... that is how a JOIN works.... maybe a simple example help you to understand better... Table_A id name 1 John Table_B (A_id is a FK to Table_A id) id A_id classname 1 1 English 2 1 History Query SELECT a.id, a.name, b.classname FROM Table_A a INNER JOIN Table_B b ON (a.id = b.A_id); An inner join essentially combines the records from two tables (A and B) based on a given join-predicate ON (a.id = b.A_id) in this case). The SQL-engine computes the Cartesian product of all records in the tables. Thus, combines each record in table A with every record in table B. Only those records in the joined table that satisfy the join predicate remain, and the final result set will be the composed of the fields that you choose from each table. In the example above the final result set will look like this (records are not duplicated, only some fields) 1 John English 1 John History then is up to you to decide how you want to display the results. Same concept apply with more tables. In this forum are several examples that you can look at to see exactly the same situation and options showing how it is solved at display time ... search for them.

just wondering why you need so many JOINS against the table content_values... seems to me that probably only one JOIN using IN() and a couple CASE's in the SELECT portion will work... but I'm just guessing.... without knowing your tables descriptions, some data example and expected results (hence your objectives) is hard to tell. GROUP BY is not necessary in the current query as DavidM said.

this is incorrect if($save){ $stmt = $db->stmt_init(); $stmt = $db->prepare("UPDATE category SET catname=?,catdesc=? WHERE cid=?"); #$db->query("UPDATE category SET catname=$catname,catdesc=$catdesc WHERE cid=$cid"); $stmt->bind_param('ssi',$catname,$catdesc,$cid); $stmt->execute(); echo "<meta http-equiv='refresh' content='0;URL=index.php?page=articles'>"; }else{ #echo "Prepare Error : ' . $db->error . ' Num Error : . $db->errno"; echo "<div align='center' id='errorText'><b>$msg</b></div>"; } review my examples again

Uh?.... don't follow... did you read and try what was suggested in my last post?.... your answer doesn't make any sense at all.... if need further help please provide better and complete status.

after having a second look to your code .. in particular to the editcat() function; I see where could be your problem; you are overwriting the edited fields value; therefore even when the UPDATE is executed correctly in reality nothing is changed.... look this extract of your code: function editcat($cid,$catname,$catdesc,$ok=false) { global $db; $cid = intval($cid); // Here you are selecting the values from the DB the first time that you click in the edit button, after select the record your display it using the form below // After submit the form this very same function is called again to execute the update with the new values... however you are executing the SELECT again overwriting // the updated values.... $result = $db->query("SELECT * FROM category WHERE cid='$cid'"); $row = $result->fetch_array(); $acid = $row['cid']; $catname = $row['catname']; $catdesc = $row['catdesc']; if (!$ok) { echo "<table width='100%' align='center' cellspacing='0' cellpadding='1'>\n"; echo "<form name='form_arg' method='post' action='index.php?page=articles&op=editCategory&cid=$acid&ok=true'>"; echo "<tr><td width='25%'><b>Name</b><td><input type='text' name='catname' size='40' maxlength='255' value=\"$catname\">\n"; echo "<tr><td><b>catdescription</b><td><input type='text' name='catdesc' size='40' maxlength='255' value=\"$catdesc\">\n"; echo "<tr><td colspan='2'><input type='submit' name='Submit' value='Modify'>\n"; echo "</form>\n"; echo "</table>\n"; } else { ......... try modifying the function a little bit like this per example: function editcat($cid,$catname,$catdesc,$ok=false) { global $db; $cid = intval($cid); // Control if the form was submitted or not if (!isset($_POST['catname'])) { $result = $db->query("SELECT * FROM category WHERE cid='$cid'"); $row = $result->fetch_array(); $acid = $row['cid']; $catname = $row['catname']; $catdesc = $row['catdesc']; } // rest of your code here ... my previous suggestions still valid too.

post your current full code again in the last code that you posted few posts ago the code shows a call to the function openTable(); (and closeTable()) which are not defined in any place in that code; it could or could not be the cause of your problem, only way to tell is looking to the whole code.

you didn't do nothing to complete the provided code... copy and paste alone is not going to help you if($save){ if ($stmt = $db->prepare("UPDATE category SET catname=?,catdesc=? WHERE cid=?")) { // Use same IF previous example to control the prepare $stmt->bind_param('ssi',$catname,$catdesc,$cid); // Use same IF previous example to control the execute $stmt->execute(); $stmt->close(); // you can drop this line to... close() occur automatic when the script end. } else { echo "Prepare Error "; // here decide what to do next } there are comments in the provided code... those were there for you to complete... other hint... you should have modified the lines: // Use same IF previous example to control the prepare $stmt->bind_param('ssi',$catname,$catdesc,$cid); to this per example: // Use same IF previous example to control the execute IF (!$stmt->bind_param('ssi',$catname,$catdesc,$cid)) { echo "Bind Param Error: " . $stmt->error . " Error # " . $stmt->errno; // here write code to execute after the error } else { //here the rest of your code... don't forget to control in the same way the execute }

your code $stmt = $db->stmt_init(); $stmt = $db->prepare("UPDATE category SET catname=?,catdesc=? WHERE cid=?"); $stmt->bind_param('ssi',$catname,$catdesc,$cid); $stmt->execute(); $stmt->close(); has a couple details... 1) you are mixing 2 different ways to declare a prepared statement... drop your line $stmt = $db->stmt_init() .. it is not necessary considering the rest of your code. 2) all the rest of the sentences (prepare, bind_param and execute) always return TRUE on success or FALSE on error, therefore you should control if each one is successfully executed, otherwise display and control the possible error using $stmt->error and probably $stmt->errno .... just an example (for you to complete): if ($stmt = $db->prepare("UPDATE category SET catname=?,catdesc=? WHERE cid=?")) { // Use same IF previous example to control the prepare $stmt->bind_param('ssi',$catname,$catdesc,$cid); // Use same IF previous example to control the execute $stmt->execute(); $stmt->close(); // you can drop this line to... close() occur automatic when the script end. } else { echo "Prepare Error : " . $stmt->error . ' Num Error : . $stmt->errno; // here decide what to do next } that should at least give you some error message if any of your sentences is failing

@Medicine: other option that also may or may not fit You can explore MySql EVENTS and validate if it fit or not your scenario http://dev.mysql.com/doc/refman/5.1/en/events.html

that is exactly what is supposed to do... echo your $query string to allow you to validate if it is correct or not... post exactly what you got.

other than what already has been suggested, I will add a few things: - You should always work with Error Reporting and Display errors enabled while in development ... you can enable it globally in your php.ini file or case by case in your scripty adding this 2 lines at the beginning (after the opening <?php) // Define how to display/report errors ini_set("display_errors", "1"); error_reporting(E_ALL); - Just take out the error suppressing from your code (@) .. that only will hide the possible errors in your code... don't hide errors... control them. - Is a good debugging practice to separate your query strings from mysql-query() ... in that way you can always echo your query string to validate what could be wrong... like: .... } else { $query = "DELETE FROM email WHERE emailaddress ='$email'"; // here you can echo your string for debugging purposes echo "Query String is : " . $query . "<br />"; mysql_query($query) or die("Mysql Query Error: " . mysql_error()); // add die() here at least as a temporary way to debug, in production you should use something better ...

where did you define $stat_bonus?.... your POST variable name is statbonus

LittleGuy already gave you an answer/solution in this thread http://www.phpfreaks.com/forums/index.php?topic=353934.0 did you try what he suggested?... try that... it should solve your problem or let you very..very close

+1 with Psycho suggestion... I will only move the time_of_day field from the table Activities (making Activities totally generic) to the table places_activities