need help to make this code shorter..

[newb]

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");
	while ($row = mysql_fetch_assoc($query)) {
	$avi0 = $row['avi_0'];
	$avi1 = $row['avi_1'];

	$mkv0 = $row['mkv_0'];
	$mkv1 = $row['mkv_1'];

	$ogm0 = $row['ogm_0'];
	$ogm1 = $row['ogm_1'];

	$mp40 = $row['mp4_0'];
	$mp41 = $row['mp4_1'];
	} 

 

this is my code atm. it is used to store file sources information inside. however, i am always expanding one format or the other (example avi or mkv).

 

how can i make this code shorter so that i dont need to keep adding more variables whenever i want to expand it? any ideas?

[Maq]

Try using variable variables:

 

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

$row = mysql_fetch_assoc($query);

foreach($row as $value => $key)
{
   $$key = $value;
}

 

This should create variables like "$avi_0" and assign it to the value of $row['avi_0'].

[kenrbnsn]

To shorten this code, I would use variable variables. I would also make each of the variables line $avi0, $avi1 into arrays.

<?php
$vars = array('avi'=>2,'mkv'=>3,'ogm'=>2,'mp4'=>2);
$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");
while ($row = mysql_fetch_assoc($query)) {
foreach ($vars as $var => $max) {
	for($i=0;$i<$max;++$i) {
		${$var}[$i] = $row["{$var}_{$i}"];
	}
}
}
?>

 

Note: this code is untested.

 

Ken

[newb]

Try using variable variables:

 

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

$row = mysql_fetch_assoc($query);

foreach($row as $value => $key)
{
   $$key = $value;
}

 

This should create variables like "$avi_0" and assign it to the value of $row['avi_0'].

 

i dont know why, but that foreach function causes serious load time issues to the point where the page will not load beyond that point.

 

while does not do this.

 

To shorten this code, I would use variable variables. I would also make each of the variables line $avi0, $avi1 into arrays.

<?php
$vars = array('avi'=>2,'mkv'=>3,'ogm'=>2,'mp4'=>2);
$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");
while ($row = mysql_fetch_assoc($query)) {
foreach ($vars as $var => $max) {
	for($i=0;$i<$max;++$i) {
		${$var}[$i] = $row["{$var}_{$i}"];
	}
}
}
?>

 

Note: this code is untested.

 

Ken

 

this didn't work at all, im guessing probably because its enwrapped around the while statements, while the rest of my code is not.

 

any other suggestions?

 

[Maq]

i dont know why, but that foreach function causes serious load time issues to the point where the page will not load beyond that point.

I don't know why, the foreach should exhaust after it traverses all the table columns.  Are you sure it's not errorring out?  *NOTE:  The variable names changed from your original code to mine.  There is an underscore separating the letters & numbers, which are the array keys.

 

Try putting error reporting on an echoing out the values to see where it stops (if it does):

ini_set ("display_errors", "1");
error_reporting(E_ALL);

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

$row = mysql_fetch_assoc($query);

foreach($row as $value => $key)
{
   $$key = $value;
   echo "$key => $value 
";
}
?>

[ignace]

Alternatively you can use extract which does somewhat the same thing like variable variables. Can you post the code that follows your while statement? Having more relevant code we maybe can give you even better advice as avi_0, avi_1 implies a bad DB design.

[mikosiko]

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

.......

it is used to store file sources information inside. however, i am always expanding one format or the other (example avi or mkv).

 

 

Sorry to go in a different direction here.... but are you saying that you are expanding the [u]table structure adding more fields to it[/u]?... if what I understood is correct seems to me that you have a design problem there... your table sources should have a static number of fields... probably you need to re-think your design and create a master-detail relationship. imho

[newb]

i dont know why, but that foreach function causes serious load time issues to the point where the page will not load beyond that point.

I don't know why, the foreach should exhaust after it traverses all the table columns.  Are you sure it's not errorring out?  *NOTE:  The variable names changed from your original code to mine.  There is an underscore separating the letters & numbers, which are the array keys.

 

Try putting error reporting on an echoing out the values to see where it stops (if it does):

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

$row = mysql_fetch_assoc($query);

foreach($row as $value => $key)
{
   $$key = $value;
   echo "$key => $value <br/>";
}
?>

 

im getting something now thanks to the echo u added in, but after that the page takes forever to load

 

also, i let the page load finally but all of my links are looking like this:

http://www.filehosting.com/mp4_4

http://www.filehosting.com/mp4_4

http://www.filehosting.com/mp4_4

http://www.filehosting.com/mp4_4

http://www.filehosting.com/mp4_4

http://www.filehosting.com/mp4_4

 

also i dont know why but it looks like this at the top:

 

34234 => id

1334234 => cat_id

3AT38DXD0WG08KVO => avi_0

0WG08KVO0WG08KVO=> avi_1

=> avi_2

0WG08KVO=> avi_3

=> avi_4

K0WG0YP5XGKYP5XGLYL34234Y=> avi_5

KYP0WG0KYP5XGLY5XGLY34234=> avi_6

=> avi_7

=> avi_8

=> avi_9

LXRPWCTD9EQOCK3V2FZGIO24 => mkv_0

2FZGID9EQOCK3O24 => mkv_1

0WG08KVO2D9EQOCK3FZGIO24 => mkv_2

D9EQOCK334234 => mkv_3

KYP5XGLY => mkv_4

=> ogm_0

=> ogm_1

=> ogm_2

=> ogm_3

=> ogm_4

=> mp4_0

=> mp4_1

=> mp4_2

=> mp4_3

=> mp4_4

 

 

any suggestions?

[newb]

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

.......

it is used to store file sources information inside. however, i am always expanding one format or the other (example avi or mkv).

 

 

Sorry to go in a different direction here.... but are you saying that you are expanding the [u]table structure adding more fields to it[/u]?... if what I understood is correct seems to me that you have a design problem there... your table sources should have a static number of fields... probably you need to re-think your design and create a master-detail relationship. imho

 

i dont want to lose any information i have alot of data stored in these tables already..so no

[Maq]

In regards to your first issue, we need to see some more code.

 

Your second issue:

also i dont know why but it looks like this at the top:

that's the $value => $key.  The key should correlate to the table name.

[newb]

OK this is the rest then.

 

	for ($i = 1; $i <= 5; $i++) {
		$s = $i - 1;
		if (!empty($avi) && $do == 0) {				
		$varname = "avi$s";
		} else 
		if (!empty($mkv) && $do == 1) {	
		$varname = "mkv$s";
		} else
		if (!empty($ogm) && $do == 2) {	
		$varname = "ogm$s";
		} else
		if (!empty($mp4) && $do == 3) {	
		$varname = "mp4$s";
		}
		$file_ext = $$varname;

		if (!empty($file_ext)) {
		$src[$i] = 'SRC '.$i.': <a target="_blank" href="'.SITE_URL.'download/'.$cid .'/'.$seotitle.'-'.substr($varname,0,-1).'-'.$id.'/index'.$i.'.html">'.srcinfo($file_ext,'file',$vbid,$id,$s,$do).'</a>';
		$size[$i] = srcinfo($file_ext,'size',$vbid,$id,$s,$do);
		$rawlink[$i] = srcinfo($file_ext,'rawlink',$vbid,$id,$s,$do);
		}
	}

[newb]

bump..still trying to figure out a solution..

 

[xyph]

Comment out loops and lines of code until you find the part that's slowing it down.

 

Nothing you've provided will cause infinite loops, but we don't even know what exec_mysql_query() does

[newb]

exec_mysql_query does the same as mysql_query. its just a function i made for it to count queries.

 

i commented out everything in the code and i only have this now:

$query = exec_mysql_query("SELECT * FROM sources WHERE cat_id = '$cid' AND id = '$id' LIMIT 1");

$row = mysql_fetch_assoc($query);

foreach($row as $value => $key)
{
   $$key = $value;
}

echo $avi_0 . "<br />";

 

echo $avi_0 gives out nothing, but i dont understand why thats the case because the field is not empty..

 

 

[PFMaBiSmAd]

i am always expanding one format or the other (example avi or mkv).

 

we maybe can give you even better advice as avi_0, avi_1 implies a bad DB design.

 

You are adding columns to your table every time you are adding another category or possible choice. That is bad database design, which is mainly why your code to retrieve the data and then make use of the data is overly complicated.

 

Your database design should have ONE row for each piece of data. For what you appear to be doing, I would have a type column that indicates avi, mkv, ogm, or mp4, and any future types you choose to add and a name column that holds the actual file name. You would then retrieve all the row(s) you are interested in, in the order that you want them. Then SIMPLY iterate over the row(s) that your query returns and output the information the way you want.

 

For what appears to be some of your data, this would look like -

 

id cat_id type     name

34234 1334234 avi 3AT38DXD0WG08KVO

34234 1334234 avi 0WG08KVO0WG08KVO

34234 1334234 avi 0WG08KVO

34234 1334234 avi K0WG0YP5XGKYP5XGLYL34234Y

34234 1334234 avi KYP0WG0KYP5XGLY5XGLY34234

34234 1334234 mkv LXRPWCTD9EQOCK3V2FZGIO24

34234 1334234 mkv 2FZGID9EQOCK3O24

34234 1334234 mkv 0WG08KVO2D9EQOCK3FZGIO24

34234 1334234 mkv D9EQOCK334234

34234 1334234 mkv KYP5XGLY

 

You only have row(s) for actual data and you can add an unlimited number of files for any type simply by inserting a row with the information in it.

[newb]

wow i havent thought of it like that..guess ill have to do some recoding then.

[newb]

is there any way i can convert my current data to the new table design quickly?

[PFMaBiSmAd]

The most straight-forward way of converting the data would be to write a script to populate a new differently named table with the data by reading through all the current rows and form the new individual rows for any non-empty data. You can then convert your code and test it off line using the new table before you make any changes to the actual live site code and database.

 

The only apparent problem I see with simply converting the data is if the id column is a key/index in the table, in which case the query you posted only needs to use WHERE id = '$id' because that would uniquely identify the row you want. However, if the id column is not a key/index in the table and you have for example, several different id's for any cat_id (the cat_id/id combination is what identifies the current row to match), then you could convert the data into a table like what I showed with no problem.

[PFMaBiSmAd]

Assuming that all the assumptions that have been made are correct, the following code should (it's tested using dummy data) help you to convert your data -

 

<?php
// existing columns and type array (array lookup to get type is faster than using substr, repeatedly, inside of the processing loop, plus this made creating dummy test data easy)-
$columns = array('avi_0'=>'avi','avi_1'=>'avi','avi_2'=>'avi','avi_3'=>'avi','avi_4'=>'avi','avi_5'=>'avi','avi_6'=>'avi',
'avi_7'=>'avi','avi_8'=>'avi','avi_9'=>'avi','mkv_0'=>'mkv','mkv_1'=>'mkv','mkv_2'=>'mkv','mkv_3'=>'mkv','mkv_4'=>'mkv',
'ogm_0'=>'ogm','ogm_1'=>'ogm','ogm_2'=>'ogm','ogm_3'=>'ogm','ogm_4'=>'ogm','mp4_0'=>'ogm','mp4_1'=>'mp4','mp4_2'=>'mp4',
'mp4_3'=>'mp4','mp4_4'=>'mp4');

$query = "SELECT * FROM sources ORDER BY cat_id,id"; // query for all the data with some ordering to it
$result = mysql_query($query) or die("Query failed: $query<br />Error: " . mysql_error());
while($row = mysql_fetch_assoc($result)){
$data = array();
foreach($columns as $index => $type){
	if(!empty($row[$index])){
		$data[] = "{$row['cat_id']},{$row['id']},'$type','$row[$index]'";
	}
}
// make a multi-value insert query
if(!empty($data)){
	$query = "INSERT INTO new_table (cat_id,id,type,name) VALUES (".implode("),(",$data).")";
	//echo "$query<br />"; // look at the resulting query
	mysql_query($query) or die("Query failed: $query<br />Error: " . mysql_error());
	echo "One row processed - cat_id: {$row['cat_id']}, id: {$row['id']}<br />";
} else {
	echo "A row with no files was skipped - cat_id: {$row['cat_id']}, id: {$row['id']}<br />";
}
}
?>

 

The new table should have an auto-increment column - file_id (or similar name) so that each row (which corresponds directly to a file now) has a unique identifier.

 

Depending on the amount of data you have, you probably need to extend or disable the php max runtime setting.

 

You will find that the suggested database structure will simplify ALL your code and queries. To insert a new file, you don't need to find an empty column of the correct type, you just insert a new row. To update/replace a file (assuming it would have a new filename) you don't need to carry along which column it is in, you just update the row with the old filename (having the suggest file_id column would make this even simpler.) To delete a file, you simply delete the row, you don't need to keep track of or find the column it is in or form a query to clear just that column. To retrieve and display the results, you just query for the  rows with the cat_id and id that you want (and if you only want one specific file type, you would include a condition to do that in the WHERE clause), in the order that you want them (assuming you want each type of file to be together in the result set), and then just loop over the rows you get. Each row gives you all the information you need to form each link you are making now. You don't need all that extra code to set intermediate variables or to later loop over those intermediate variables.

 

And I almost forgot, you apparently ran into performance problems when using the variable variable code (though it was more likely a logic error somewhere in using the resulting values or you accidentally overwrote one of your program variables by using variable variables), using variable variables are three times slower than using an array variable and are undesirable for processing large amounts of data or using inside of a loop.

[newb]

thanks for the info my code is preettty sloppy lol.

[newb]

i have over 51,000 rows that need to be converted. is there any way i can split this script up so that it doesnt time out?

 

btw, thanks for the code it works awesome if i didnt have so many rows lol.

[newb]

also i seem to have ran into another issue..

 

the script only works for rows that have one source..

when it has 2 or more sources, i get this error.

 

Query failed: INSERT INTO newtable (cat_id,id,format,source,host) VALUES (2,16455,'avi','94VXBJXA'),(2,16455,'mkv','XWZCEKQE',1)

Error: Column count doesn't match value count at row 1

 

why?

[newb]

nvmd fixed with another foreach statement..this can be closed