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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
angelicodin
Forum Commoner
Posts: 81
Joined: Fri Nov 13, 2009 3:17 am
Location: Oregon, USA

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

Post 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.
Last edited by angelicodin on Fri Nov 13, 2009 4:16 am, edited 1 time in total.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Problem with my loops(PHP+MySQL)

Post 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.
User avatar
angelicodin
Forum Commoner
Posts: 81
Joined: Fri Nov 13, 2009 3:17 am
Location: Oregon, USA

Re: Problem with my loops(PHP+MySQL)

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Problem with my loops(PHP+MySQL)

Post 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.
User avatar
angelicodin
Forum Commoner
Posts: 81
Joined: Fri Nov 13, 2009 3:17 am
Location: Oregon, USA

Re: Problem with my loops(PHP+MySQL)

Post 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?
Post Reply