Some Poster55

Ah, made some good progress on this. The below code accomplishes what I need: // $table_name_array is a large array which holds the name of every table in the database for ($x = 0; $x < sizeof($table_name_array); $x++) { // Cycle through each table and preform a giant union between the 6th data values on the specified date $sql .= "(SELECT '$table_name_array[$x]' as name, SUBSTRING_INDEX(SUBSTRING_INDEX(data, '\n', 2),',',-1) + 0 as data FROM $table_name_array[$x] WHERE date='$user_input')"; // Preform a UNION every time in the loop except the very last if ($x < sizeof($table_name_array)-1) { $sql .= " UNION " ; } } // Finally, after the giant string of UNIONs, sort it to get the top 3 values $sql .= "ORDER BY data DESC LIMIT 3"; $result = mysql_query($sql); // Display the top 3 "6th data values" in array form while ($row = mysql_fetch_assoc($result)) { print_r($row); } Although this script is considerably faster and only takes a few seconds, I'd be curious as to if anyone would be able to improve it even further, as getting it down to just 1 second would be preferable for a live page. Or, if I actually accomplished it in the best way, let me know that too. Thanks!

That above query actually worked so well I'm attempting to refine another one of my slow pages with something similar. This time the setup is a bit different - There's about 1000 tables in the database. Each table is named the name of a specific user ("Bob", "Dan", etc.). Each table contains 3 columns: "id" (primary key), "date", and "data". There is exactly 1 row for each day since the user created their account. Example: ...And so on, for about a thousand tables. My script takes a user-inputted day (like "Feb 12, 2010") and needs to display the top 3 users who had the highest 6th data value for that day. Again, this is similar to my previous script above, but what's puzzling me is how this could be accomplished across multiple tables. Here is my current inefficient script which completes the task. It selects the user-specified row from every table in the database, parses each row, then sorts the parsed values in descending order, then only display the top 3: for ($x = 0; $x < 1000; $x++) { // Fetch the row in every table for the date the user specifies // $table_name_array is a large array which holds the name of every table in the database $row = mysql_fetch_array(mysql_query("SELECT * FROM $table_name_array[$x] WHERE date='$user_input'")); // Explode the data row to easily be able to retrieve its 6th data value $data_explode = explode("\n",$row['data']); foreach ($data_explode as $x=>$y) { $data[$x] = explode(",",$y); } // Place each user's name (same as the table name) and 6th data value into a large "storage" array $storage[] = array('name' => $table_name_array[$x], '6th_data_value' => $data[1][2]); } // Sort the large "storage" array in descending order foreach ($storage as $key => $row) { $sorter[$key] = $row['6th_data_value']; } array_multisort($sorter, SORT_DESC, $storage); // Echo the top 3 results in the sorted "storage" array for ($x = 0; $x < 3; $x++) { echo $x.") ".$storage[$x]['name']." - ".$storage[$x]['6th_data_value']; } Though this again takes too long to effectively use. Essentially what I'd need would a query which does: (Fetch an array with the both the table's name (same as the user's name) and 6th data value for only the top 3 "6th data values" for the user-specified day.) The script would then allow me to display (for example, using "Feb 12, 2010" with the above data): 1) Dan - 45668 2) Bob - 4978 So instead of fetching the user-specified row from every table, then sorting every 6th data value to get the Top 3 - I'm looking for a query which simply fetches the top 3 "6th data values" for the user-specified row in every table. Sorry if this is something easy - I've searched around quite a lot and nothing has worked, so I'd again be very appreciative of any help. Thanks!

Ah, got it: SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 3),'\n',-1) + 0 as data FROM tablename HAVING data != 999 ORDER BY data DESC LIMIT 3 Thanks for all the help!

Good stuff, thanks again. Really thought I'd be able to figure this next part out - but it doesn't seem to be working. Now I'm trying to reach the 4th data value instead of the 6th. Since it was still in the second row I kept the innermost SUBSTRING_INDEX as is, but modified the count on the outer SUBSTRING_INDEX from "-1" to "1" (according to SUBSTRING_INDEX's documentation, I believe this should've fetched only the leftmost value, just as "-1" fetches the rightmost value): However, oddly all this actually did was sort it by the very first value - so going along with my above example, I got "554, 88, 88" instead of "456, 456, 56". Any help would be appreciated.

Thanks, exactly what I was looking for! I do have one final question though: would there be a way to ignore the 6th data value if it is "999"? I tried the following, but it didn't work (nor did it really seem like something that would work - but just to give you an idea of what I'm looking for):

Hi, Currently have an issue which I'd appreciate some advice on. I have a table structured as shown below, with a "name" column that holds a user's name, and a "data" column which holds numerous comma separated values (with a new line \n after each 3rd value): ...And so on, for thousands of users. I need to have a page on my site which displays the top 3 users who have the highest 6th data value (the bolded value above). For instance, using the above example data, the page would display: 1) User 2 - 4568 2) User 3 - 868 3) User 1 - 55 My first attempt was to select every row in the table, parse each row, then sort the parsed values in descending order, then only display the top 3: $result = mysql_query("SELECT * FROM myTable"); // Cycle through each row in myTable to grab its data values while ($row = mysql_fetch_array($result)) { // Explode the data row to easily be able to retrieve its 6th data value $data_explode = explode("\n",$row['data']); foreach ($data_explode as $x=>$y) { $data[$x] = explode(",",$y); } // Place each user's name and 6th data value into a large "storage" array $storage[] = array('name' => $row['name'], '6th_data_value' => $data[1][2]); } // Sort the large "storage" array in descending order foreach ($storage as $key => $row) { $sorter[$key] = $row['6th_data_value']; } array_multisort($sorter, SORT_DESC, $storage); // Echo the top 3 results in the sorted "storage" array for ($x = 0; $x < 3; $x++) { echo $x.") ".$storage[$x]['name']." - ".$storage[$x]['6th_data_value']; } This worked - however, as I was fetching thousands of rows, the script takes too long (10+ seconds) to effectively use on a page. I was wondering if there was any way in which I wouldn't have to fetch the thousands of user rows (which takes a long time), just to get the top 3 "6th data values"? I know that if the specified data value was in a column of its own, then I could easily change the SQL statement to SORT DESC and LIMIT 3 before fetching, so that it would only fetch the top 3 values. But since the 6th data value I need is buried within other values, I was hoping that someone would have a way to still only fetch from the rows I need (and not the entire table), or at least some other way to improve the scripts timing? Many thanks in advance!

Bump.

Correct, I have a file called "configure.php" which only has this in it: I then put: At the top of all my scripts. I've also heard some people saying to always use mysql_close() at the end of my script, but as I thought MySQL connections closed when a page loads, this shouldn't be necessary?

No, I don't believe so. I always connect to the database using mysql_connect(), and never mysql_pconnect(), if that's what makes a persistent connection. Checking my phpinfo() file, the mysql.allow_persistent directive is set to "Off" as well (I turned it off via the PHP INI file a while ago just to be safe).

Hi, Have a bit of an issue here and would appreciate some advice. Occasionally I'll get this error message on my site: Some days my site will be completely fine, but every so often for around ~20 minutes it will simply display the above error. I've searched around quite a bit for solutions, but was unable to locate any. Here's my phpinfo() page if that helps: http://runetrack.com/phpinfo.php Any ideas as to how to solve this or even what it could be caused by? Many thanks in advance!

Hi, Basically, I'm looking for some help with resizing a png image that has a transparent background. Here's what I'm currently working with: <?php $image1 = imagecreatefrompng("first_image.png"); $width = 59; $height = 112; $newwidth=70; $newheight=112; $image2=imagecreatefrompng("blank_background.png"); imagecopyresampled($image2,$image1,0,0,0,0,$newwidth,$newheight,$width,$height); imagepng($image2,"result.png",9); imagedestroy($image1); imagedestroy($image2); ?> The "first_image.png" file is the image with a transparent background which I need to resize. The "blank_background.png" is a completely transparent and blank image which I'm using to resize the first image using the imagecopyresampled() function. However, upon running the script, I end up with my original image perfectly resized - but with a white background instead of a transparent background. How can I resize the original image, but with retaining the transparent background? Help would be greatly appreciated, thanks!

Ah, makes sense..thanks!

Hi, I have a page where a user types in their username, and gets sent to another page where their account information is extracted from the database and displayed on the page. My database is set up so that each user has their own table. This all works fine, except obviously when someone who does not have their own table in the database types in and submits their name. Basically, I want the script to first check if the user's table already exists. But if it doesn't, I need it to create a table with that username as the table's name. Here's what I was trying: $user_table = mysql_real_escape_string($_POST["user_select"]); $result = mysql_query("SELECT * FROM $user_table WHERE id='1'") or die($create == 1); if ($create == 1) { mysql_query("CREATE TABLE $user_table( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(20))") or die(mysql_error()); } Basically, I just need to define a variable as a number if the original mysql query dies - but the above piece of code doesn't seem to be creating a new table if a non-existing name is submitted. I imagine it's possible to directly place the entire create table function within the Or Die(), but I need to specifically define $create as "1" if the original mysql query fails, as the $create variable will be used in other places. Help would be greatly appreciated, thanks!

Hi, I'll try to explain this as best I can, but basically I need to create a GD image based off of a variable entered in the page's URL. I've seen a number of sites do this in the past, so I know it's possible. So for example, if I were to visit http://website.com/HelloWorld/image.png - it would show an image containing the text "HelloWorld". Whatever that directory name would be changed to in the URL would then be displayed on the picture. So if I changed the URL to http://website.com/ByeWorld/image.png - it would show an image containing the text "ByeWorld". If I changed the URL to http://website.com/HeyWorld/image.png - it would show an image containing the text "HeyWorld" - And so on... I understand how to do this using a regular php file as so: <?php header("Content-type: image/png"); $im = imagecreate(510, 20); $bg = imagecolorallocate($im, 0, 0, 0); //Get the current directory of the page $url = $_SERVER["REQUEST_URI"]; $text = explode("/",$url); //Write the page's current directory on the image $textcolor = imagecolorallocate($im, 0, 0, 255); imagestring($im, 5, 0, 0, $text[1], $textcolor); imagepng($im); imagedestroy($im); ?> However, I'm baffled as to how this works by just using a file with a .png extension. Following in the above example, my code will work as desired from http://website.com/HelloWorld/image.php and correctly write an image with the text "HelloWorld", but what I need is for it to be at http://website.com/HelloWorld/image.png . The code above is exactly what I need, it's just I need the page to have a .png extension instead of a .php extension. Any ideas? Thanks much in advance!