Page 1 of 1

[RESOLVED]Problem with my loops(PHP+MySQL)

Posted: Fri Nov 13, 2009 3:20 am
by angelicodin
Hi ya guys, first time here, and I think I'm going to have a great time here, as I have already read some of the threads and seems most people here are vary helpful.

Anyway here is what is going on. I have a bit of code that is not working right, naturally.

Code: Select all

 <?php
include("sql.php"); //sql connect and DB select
$sql = "SELECT * FROM misc ORDER BY `misc`.`id` DESC ";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ //rows from misc video table
    $sql2 = "SELECT * FROM `misctype` ORDER BY `misctype`.`id` DESC ";
    $result2 = mysql_query($sql2);
    while($row7 = mysql_fetch_row($result2)){ //figure out what is the file type and set an array with the resolution and file extension
        if(strtolower($row['type']) === strtolower($row2[1])){
            $type = array($row2[0],$row2[1],$row2[2],$row2[3],$row2[4]);
        }
    }
        echo "This row type is: ".$type[1]."<br>"; //echo the type of the video (figured you didn't need to see all the row output data ;p
}
?>
While this dose not give me any syntax errors or sql errors it only allows the array data to be the first row of the $sql2 query, at least, at far as I can tell with some echo debugging.

What I'm trying to do here is have a search result return embedded videos from a misc section where the file types are different and the resolutions are different so I have another table that defines the type, extensions and resolutions for the videos then is associated to the main misc video table, of course all done with a CMS I'm building ;p

If there is any other information you need I'll be happy to offer what I can and any other code that might help.

Thank you for taking the time to look at this.

Re: Problem with my loops(PHP+MySQL)

Posted: Fri Nov 13, 2009 3:42 am
by onion2k
You could replace all that with a very simple JOIN in the SQL.

Something like:

[sql]SELECT `misc`.*, `misctype`.`resolution`, `misctype`.`extension` FROM `misc` LEFT JOIN `misctype` ON `misctype`.`id` = `misc`.`type`ORDER BY `misc`.`id` DESC[/sql]

Also, something important to note: Stop accessing your database fields using array indexes (eg $row2[0]). All your code will break if someone adds a new column in the middle of the table. Eg

Current schema = id, resolution, extension
Your code: $row[0] = id, $row[1] = resolution, $row[2] = extension

New schema = id, mimetype, resolution, extension
Your code: $row[0] = id, $row[1] = mimetype, $row[2] = resolution

Uh oh! It's returning the wrong things. It's all broken! Use the column names instead.

Re: Problem with my loops(PHP+MySQL)

Posted: Fri Nov 13, 2009 3:47 am
by angelicodin
I'll give that a try. I'll have to read up on that function of sql, and thanks for the response. And vary good idea about always doing an assoc array. ;p

Re: Problem with my loops(PHP+MySQL)

Posted: Fri Nov 13, 2009 3:53 am
by onion2k
angelicodin wrote:And vary good idea about always doing an assoc array. ;p
Actually, I'd recommend accessing data using "objects" with mysql_fetch_object(). Eg

Code: Select all

$result = mysql_query($sql,$databaseLink);
while ($record = mysql_fetch_object($result)) {
  echo $record->title;
}
It's technically the same as the associative array method just with different syntax, but once you get on to using a database abstraction library such as ADODB (and you will, eventually) you'll have to use the object style. Might as well use it from the beginning.

Re: Problem with my loops(PHP+MySQL)

Posted: Fri Nov 13, 2009 4:10 am
by angelicodin
Well I guess I'm going to the book store and picking on a book or two on that, never heard of it until now. ;p Any recommendations?