Psycho

You can index ANY column in your table. Indexing a column makes it faster when querying with that data where the MySQL engine has to do comparisons against those columns (i.e. in WHERE clauses, joins, etc.). Your primary key fields (i.e. auto-increments) are typically indexed by default. And you should index the foreign keys as well so doing JOINS are quicker. But, don't needlessly index columns that aren't used much for comparisons. To index additional columns in a table, go to the table in PHPMyAdmin and select the "Structure" tab. You should have two grids on the page. The top grid shows all the fields in the table with a list of properties and actions available for each. The bottom grid shows all the indexed fields. To index a field, find it in the top grid and on the very right-hand side you should see an icon of a table with a lightning bolt on it. Click it and that field will be indexed. However, I'm curious why your queries are taking so long. Unless you have a ton of records, 90 seconds is way too long. Are you sure you are using the correct field types for your data?

Well, I think you are making it more difficult than it needs to be. But, it really depends on your requirements. But, my approach is that when a user edits the data for a category you would simply delete all existing records and add the ones submitted in the form. So, let's say Category A currently has three values as follows: 1. Apple 2. Banana 3. Cherry A user then selects to edit Category 1 and you populate three fields with the current values and allow the user to edit the current values and add news ones. So, lets say the user leaves the first field alone, removes the value from field #2, changes the value in field #3 and lastly adds a new filed with a new value. You are then left with: 1. Apple 2. 3. Grapes 4. Pear So, when the user submits the page don't try and figure out what changed. Instead just delete everything currently associated with Category A and add all the values in the form.

Just to clarify. The form may have multiple "categories" and each category can have multiple pieces of data. What isn't clear from your explanation is whether the categories will always be added as new or if they are being edited. In either case the solution is similar but will have to be handled differently. For the purposes of providing a solution I will assume that you are editing existing categories (not adding new ones) The solution is to simply create the data fields as a mulch-dimensional array value with the first key associated with the primary key of the category. In your example above you have two categories (category A & Category B) with ids of 1 & 2. So, all the data fields for category A will look like this: <input type="text" name="data[1][]" /> and the ones for category 2 will look like this <input type="text" name="data[2][]" /> Then when you process the form you can access the arrays of the new/updated values. Since I am assuming you are editing existing records you would have to DELETE any records associated with the records being updated because you can't determine what records have changed vs. added ones. So the processing code might look something like this //Create temp arrays $deteleIDsAry = array(); $insertValues = array(); //Process POST data foreach($_POST['data'] as $categoryID => $dataAry) { //Add category ID to array $deteleIDsAry[] = intval($categoryID); foreach($dataAry as $dataValue) { $dataValue = mysql_real_escape_string($dataValue); $insertValues[] "('{$categoryID}', '{$dataValue}')"; } } $deteleIDsStr = implode(',' $deteleIDsAry); //Delete existing data records for the categories that were updated $query = "DELETE FROM data_entry_table WHERE catid IN($deteleIDsStr)"; $result = mysql_query($query) or die(mysql_error()); //Create ONE Insert query to add all the records for the categories that were updated $query = "INSERT INTO data_entry_table (catid, data) VALUES " . implode(', ', $insertValues); $result = mysql_query($query) or die(mysql_error());

I suspect you are running the print_r() AFTER the foreach loop. So the variable you are assigning the data to on each iteration is being overwritten. Then, when the loop completes, the variable only holds the data from the last iteration. OK, I assume that there would always be three sets of fields and in some cases you won't have data for some, correct? Then just run some validation on each iteration of the loop to figure out if some records should be excluded A rough example: $inputValues = array(); foreach($_POST['hdds'] as $hdd_data) { $brand = trim($hdd_data['hddbrand']); $type = trim($hdd_data['hddtype']); $serial = trim($hdd_data['hddsn']); $notes = trim($hdd_data['hddnotes']); ## PERFORM VALIDATIONS - Add What you need, these are examples //Check req'd fields if(empty($brand) || empty($type) || empty($serial)) { continue; //Skip this record } //Format data into a SQL insert value $inputValues[] = sprint_f("('%s', '%s', '%s', '%s')", mysql_real_escape_string($brand), mysql_real_escape_string($type), mysql_real_escape_string($serial), mysql_real_escape_string($notes) ); } //Prepare complete INSERT statement $query = "INSERT INTO table_name (brand, type, serial_no, notes) VALUES " . implode(', ', $inputValues);

Are you running this query in PHPMyAdmin (or something similar) or are you running it through PHP code. If only through PHP try running it in your database management app. Also, a couple other things: 1. Try changing the WHERE clause for the two date checks to a BETWEEN clause instead of two separate checks. 2. Try indexing the columns that you are using in your WHERE and JOIN clauses. Lastly, you should run your query as an "EXPLAIN" query. Preface the select with "EXPLAIN". The output gives details about the efficiency of your query that you can improve upon. http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

Just to add to his comment function RandomLine($filename, $dateStr) { $matchingLines = array(); foreach(file($filename) as $line) { if(strpos($line, $dateStr) !== false) { $matchingLines[] = $line } return $matchingLines[array_rand($matchingLines)] ; } Although, I guess you could also unset() a line from the original array if the date doesn't exist. If the process take any significant amount of time to run, you might look at that as an option.

There are different formats of bar codes. You need to determine what format you need first. But, this might have all that you need: http://www.barcodephp.com/en I don't know anything about that software, it was just the first hit in a google search.

microtime()

Here is a revision of your script that corrects the query and adds some security and debugging code <?php include ("dbconnect.php"); $link = dbconnect(); $cat = mysql_real_escape_string($_GET['catagory']); $id = intval($_GET['id']); //Create/Run query $query = "SELECT `link` FROM `{$cat}` WHERE `id`='{$id}' LIMIT 1"; $result = mysql_query($query) or die(mysql_error()); //Validate/Process results if(!mysql_num_rows()) { die("No results for id '{$id}'"); } $file = mysql_result($result, 0); if(!is_file($file)) { die("Result '{$file}' was not a file"); } $basename = basename($file); $filesize = filesize($file); //Output results header("Content-type: octet/stream"); header("Content-Disposition: attachment; filename='{$basename}';"); header("Content-Length: {$filesize}"); readfile($file); exit; ?>

The file contents you just showed is a clear explanation of the problem. You are NOT downloading the file. Instead, the download contains an error that was generated when trying to send the file to the user. There is an error generated when attempting to run filesize() and when trying to run readfile(). You can ignore the header error - that's only because teh first error sent output tot he browser. My guess is your query is failing because of the '*' being right next to the 'FROM'. But, you shouldn't be using '*' anyway since you don't need all the fields Edit: there are several other issues as well. You are not escaping the user input which could lead to SQL Injection attacks. I assume that ID should be in INT value.

Yeah, that was just an oversight. Didn't meant to do that. That's what happens when you modify a lot of code and can't actually test it.

There are inefficiencies in that query. 1. Why are you using CAST() on the date value in the select value? All values returned from a MySQL query are treated as text anyway. It's only useful to cast input values to insert into certain data type fields or to compare values that are of different types 2. Why are you using CAST() on the date field in the WHERE caluse. Are they of different types? If not, don't use CAST(). 3. Not sure, but putting all your tables as a list and then trying to do the JOINs in the WHERE clauses might be problematic too. Here is an attempt at revising your query, but I really have no way of knowing if it will work SELECT sr.scheduleid, CAST(sr.scheduledate as Date) AS schedule_date, s.id, s.gf, s.schedule_desc, DISTINCT srd.record_date AS academic_date, srd.student_id, rc.rc_period_id, rc.start_date, rc.end_date, FROM schedule_room AS sr JOIN schedules AS s ON s.id =sr.scheduleid JOIN studentrecord_daily AS srd ON srd.record_date = sr.scheduledate JOIN rc ON srd.record_date BETWEEN rc.start_date AND rc.end_date WHERE srd.student_id = '3' AND rc.rc_period_id = '27'

You shouldn't need to update anything. Instead you can set the date the record is created. Then when you query the database you can dynamically determine the status based upon the created date of the records and the current date.

I still think the query I provided is correct per what you have requested and is much, much simpler. I assume that there is only one record per user in the wp_users table. So, based upon your requirements you want the results to contain one record for every user who: - Has a meta_key = "wp_s2member_custom_fields" - Has a meta_value LIKE "%s2member_level%"' The query I provided will do that. I tested it with a very small set of data and it worked. But, I don't have all the information about possible values in your database that might cause the query not to pull the correct data. I am more than willing to provide more assistance, but I really need some real data. Can you do an export from your two tables for a small subset of records? Do something like 20 users and all their associated records from the meta table.

As I have said COUNTLESS TIMES you have not provided the data requested and I am working off of a lot of assumptions. The original query was a mess and I have tried to improve it as I was able to ascertain more information from the various posts. If the count is off either you made a modification or I still don't understand your data structure. Either way, it's your fault.

I'm not going to go through and rewrite all your code. But, you should set up your data into an array or database. Then use that for all your processing: A very simple format $products = array( 'Four 100 Watt Reg' => 2.39, 'Eight 100 Watt Reg' => 2.39, 'Four 100 Watt Long' => 2.39, 'Four 100 Watt Long' => 2.39, ) Then you can use that data to: create the input fields and process the data. I would create the input fields something like this so they are an array (much easier to process) foreach($products as $prodName => $price) { echo "<input type='checkbox' name='products[]' value='$prodName'> $prodName<br>\n"; } Then to process the post data into a total $total = 0; foreach($_POST['products'] as $prodName) { $total += $products[$prodName]; }

There is nothing wrong with what he has. The variable will be parsed inside the double quoted string.

Still not clear. Is $row the value from the form or is it the result from the DB query? Would be nice to see some code. But, based upon my previous statemetn I'd be doing something like this: $includeFile = isset($_SESSION['email']); if(!$includeFile) { //Use form input to run db query and set //$includeFile as appropriate } if($includeFile) { require 'myfile'; }

OK< I've made quite a few changes in your original code as it was very difficult to follow. It is in a more logical flow than before. For example I made one function to retrieve the answers into an array. But, you shouldn't be storing your answers in this fashion anyway. There should be a separate table for the answers with a foreign key reference back to the question and each answer should have it's own unique ID. But, I went ahead and worked with what you have. As I said, the radio groups should have names such as "name[iD]" where ID is the question ID. You can then create a loop in the processing script to loop through the values using foreach($_POST['answer'] as $id => $tekst) Anyway, like I said, I made a lot of changes. I couldn't test this as I don't have your database. So, there may be some minor typos and such. But the logic should be good. I assumed that the ID for the answers is an auto-increment field and that the date is automatically populated in the database. So, I left them both out of the insert query. <?php function odgovor($string) { return array_slice(explode("/", $string), 0, 2) } require_once('Connections/strana.php'); mysql_select_db($database_strana, $strana); $output = ''; //Temp var for the output if(isset($_POST['answer'])) { ### THE FORM WAS SUBMITTED. INSERT DATA //Process the post data into INSERT data $user_id = intval($_COOKIE['user_id']); $values = array(); //Array to hold insert values for query foreach($_POST['answer'] as $id => $tekst) { //Sanitize the data. Also add any validation that you want, i.e. ensure answer has a value) //Discard any records with invalid data $question_id = intval($id); $answer - mysql_real_escape_string(trim($tekst)) $values[] = "('$user_id', '$question_id ', '$answer')"; } //Create and run the insert query if(count($values)) { $query = "INSERT INTO answer_table (`user_id`, `question_id`, `answer`) VALUES . " implode(', ', $values); $result = mysql_query($query) or die(mysql_error()); $output = "Your test results have been submitted"; } } else { ### THE FORM WAS NOTE SUBMITTED. CREATE FORM. $query = "SELECT prasanje_id, prasanje_tekst, odgovor FROM prasanja WHERE tip = 2"; $result = mysql_query($query) or die(mysql_error()); while ($row=mysql_fetch_assoc($result)) { $id = $row['prasanje_id']; $prasanje = $row['prasanje_tekst']; $tekst = odgovor($row['odgovor']); //Return an array with 2 answers $output .= "<form action='' method='post' enctype='multipart/form-data' name='form1'>\n"; $output .= "<table>\n"; $output .= " <tr><td class='formaP'>{$prasanje}</td></tr>\n"; $output .= " <tr>\n"; $output .= " <td class='formaO'>\n"; $output .= " <p>\n"; $output .= " <label><input type='radio' name='answer[{$id}]' value='{$tekst[0]}' /> {$tekst[0]}</label>\n"; $output .= " <label><input type='radio' name='answer[{$id}]' value='{$tekst[1]}' /> {$tekst[1]}</label>\n"; $output .= " </p>\n"; $output .= " </td>\n"; $output .= " </tr>\n"; $output .= "</table>\n"; $output .= "<input align='left' type='submit' name='submit' value='Внеси' />\n"; $output .= "</form>\n"; } } ?> <html> <body> <link href="css/styles.css" rel="stylesheet" type="text/css" /> <table width="100%" height="100%" style="margin-left:auto;margin-right:auto;" border="0"> <tr> <td align="center"> <?php echo $output; ?> </td> </tr> </table> </body> </html>

Don't create your fields with names such as "field#" where '#' is an identification for that field. Instead, create them as arrays using "field[]" or "field['#']". Anyway, I don't see anything in that script that would process the code. Are you planning to have the form submit back to the same page or a separate page? Also, I'm confused by the output. It looks like you are creating two duplicate answers for each question. So, what difference does it make what radio button the user selects? It will submit the same answer. Plus, the HTML code appears to be a mess.

And please state what the values for $row and $_SESSION['email'] could contain. If you want to do something based upon a session value OR a database result then you should do the Session value check first. If that fails THEN you would do the query. No need to run a query if the session condition is true.

I absolutely changed the JOINs - the previous ones were flawed. I don't think you were understanding some of the information I was stating previously. Look at this JOIN JOIN wp_users u ON um1.user_id=um2.user_id That makes no sense. How can you join table 'u' based upon a condition between table 'um1' and table 'um2'? Again, I am willing to help, but you have still not provided the details that I need. Which is detailed information about the structure of the data. So, I am stuck having to make assumptions. I will give it one last try. If this is not what you need and you fail to provide more information I'm done. I have built a couple sample tables based on what I think your structure and data is. I have also modified the query to a more logical format. This query starts on the user table and then JOINs records from the meta table in two instances (where there is a meta_key = "wp_s2member_custom_fields" AND where there meta_value LIKE "%s2member_level%"). Based upon your comments this is what you want. I also have to make an assumptions based upon how you determine the subscription type since you haven't stated that. I assume it is the KEY s2member_level# in the meta value, but I don't know what values are for what. Also, I still don't see where you want to use the email or login in your output. If you don't need those in the output then remove them from the query as they are not needed. //Create array to define subscription levels //these are just guesses $subscriptionLevels = array( 's2member_level4' => 'Yearly', 's2member_level3' => 'Semi-annual', 's2member_level2' => 'Monthly', 's2member_level1' => 'Weekly' ); //Create and run query $query = 'SELECT u.ID,u.user_login, u.user_email, um1.meta_value as custom, um2.meta_value as level FROM wp_users u JOIN wp_usermeta um1 ON u.ID = um1.user_id AND um1.meta_key = "wp_s2member_custom_fields" JOIN wp_usermeta um2 ON u.ID = um2.user_id AND um2.meta_value LIKE "%s2member_level%"'; $result = mysql_query($query); //Process results into temp array $regionData = array(); while($row = mysql_fetch_assoc($result)) { //var_dump($row); $custom = unserialize($row['custom']); $user_region = $custom['county']; $level = unserialize($row['level']); $level_desc = key($level); //E.g. s2member_level3 $user_level = $subscriptionLevels[$level_desc]; if(!isset($regionData[$user_region])) { $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0); } $regionData[$user_region][$user_level]++ } //Output the results foreach ($regionData as $region => $data) { echo "Region {$region}<br>\n"; foreach($data as $subscription_type => $member_count) { echo "{$subscription_type}: {$member_count} members<br>\n"; } }

You were getting all those additional rows because the JOINs were not done properly. That query was joining every row on every other row. Did you even try the code I posted? I only hobbled that query together because even after NUMEROUS requests you still have not provided information I have requested multiple times now regarding the structure of the data. You could have had this problem solved days ago if you provided that information. If I had a clearer picture of your database this would be so much easier. As for the aliases used that way, that basic stuff so I'm not sure how you've seen them used. After additional consideration, I would probably flip the query to start with the user table and then join the meta table on that two times. But again, that is just a guess because you still have not provided enough details about the table and data structure. I would be happy to provide a better query if you want to attach an export of those two tables.

As PFMaBiSmAd has stated you have duplicate column names in the results. But, I still think your query is malformed and is inefficient. For example, look at the JOIN condition for the "wp_users" table JOIN wp_users u ON um1.user_id=um2.user_id That is joining the table based upon criteria of the other two tables - which has no effect. The only reason that table is being joined correctly is due to criteria in the WHERE clause. Plus, you are JOINing every record in the two "meta_value" tables together. The only reason you get one record per user is the GROUP BY clause which wouldn't be needed if the JOINs were correct. This query should be more efficient AND should resolve your problem. You will just need to change the logic to reference the aliased names. But, I'm still curious why you are querying all those fields when you are only using the meta field for county in the results, but oh well $query = 'SELECT um1.meta_value as custom, um2.meta_value as level, u.ID,u.user_login, u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 ON um1.user_id = um2.user_id JOIN wp_users u ON um1.user_id = u.ID WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%"'; $result = mysql_query($query); $region = array(); while($row = mysql_fetch_assoc($result)) { $meta_value = unserialize($row['custom']); $region[$meta_value['county']]++; //var_dump($row); } foreach ($region as $key => $value) { echo "Region $key: $value members<br>"; }

If you are adding the class 'MVP' to the element then why do you need a function? maybe I am completely misunderstanding what you are doing. BUt, that function will only return the value 'MVP' if it is passed the value 'MVP', so I don't really see what you are wanting. The only thing I can think of is that you have a column in that table, called 'MVP', and if the record is an MVP then the value in that field is 'MVP', else it is some other value. That's really a poor implemetnation. If you have a column to identify if a record is an MVP or not, then it should be an INT type with a 0 or 1 (where 1 is true and the record is an MVP) As for the code you just posted, it's kind of a waste to define variables just to use them once in that loop. But, if my assumption above is correct and you want to set the class of the TDs as 'MVP' if the value of the 'MVP' field is 'MVP' then this would work (no function needed). while($row = mysql_fetch_assoc($sql)) { $class = ($row['MVP']=='MVP') ? 'MVP' : ''; echo "<tr>\n"; echo " <td class='{$class}'>{$row['first_name']}</td>\n"; echo " <td class='{$class}'>{$row['last_name']}</td>\n"; echo " <td class='{$class}'>{$row['team']}</td>\n"; echo " <td class='{$class}'>{$row['MVP']}</td>\n"; echo "</tr>\n"; } And, if you changed that field to an int and used 0/1 this would work to change the class and set the MVP value while($row = mysql_fetch_assoc($sql)) { $class = ($row['MVP']) ? 'MVP' : ''; $mvpText = ($row['MVP']) ? 'MVP' : ''; echo "<tr>\n"; echo " <td class='{$class}'>{$row['first_name']}</td>\n"; echo " <td class='{$class}'>{$row['last_name']}</td>\n"; echo " <td class='{$class}'>{$row['team']}</td>\n"; echo " <td class='{$class}'>{$mvpText}</td>\n"; echo "</tr>\n"; }