While statement not looping?

squidnunc

Hi,

This is my first attempt at PHP aside from includes, virtuals, etc. I am trying to display the fields from a MYSQL table I established from a text file datafeed from AllPosters.

I'm using the movie Casablanca as a test. There are 6 posters from Casablanca in the database. With the faulty code I'm using, the six posters all come out the same as you can see in the link below:

http://www.rhodemap.com/rm/datafeeds/casablanca.php

Here is the code I used (minus the database connection info):

<?php
/ Connecting, selecting database /
$link = mysql_connect("host", "name", "pwd")
or die("Could not connect");
mysql_select_db("posters") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT * FROM movies WHERE Title ='Casablanca' ";
$result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */
print "<table width=100%>\n";
    print "\t<tr>\n";
    print "\t\t<td align=center width=33%>\n";
while ($row = mysql_fetch_array($result)) {
    $cat_name = $row["Category Name"];
    $cat_number = $row["Category Number"];
    $prod_number = $row["Product Number"];
    $title = $row["Title"];
    $artist = $row["Artist"];
    $file_name = $row["File Name"];
    $directory = $row["Directory"];
    $img_height = $row["Image Height"];
    $img_width = $row["Image Width"];
    $thumb_height = $row["Thumbnail Height"];
    $thumb_width = $row["Thumbnail Width"];
    $poster_height = $row["Poster Height"];
    $poster_width = $row["Poster Width"];
    $prod_type = $row["Product Type"];
    $mounted = $row["Can Be Mounted"];
    $framed = $row["Can Be Framed"];
    $quick_framed = $row["Quickframable"];
    print "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=$p

rod_number\"><IMG
SRC=\"http://images.allposters.com/images/$directory/$file_name\"
WIDTH=$thumb_width HEIGHT=$thumb_height BORDER=0></A>\n";
print "\t<br><A
HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=$p
rod_number\">$title</A>\n";
print "\t<br>$prod_type\n";
print "\t<br>$poster_height x $poster_width<br>\n";
}
print "\t\t</td>\n";
print "\t</tr>\n";
print "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);

</BODY>
</HTML>

Please let me know how I messed up the while statement so it isn't looping.

Thanks,
Keith

tomhath

What are the column names in your table? The associative array indexes you are using have spaces in them. How did you get column names with embedded spaces?

Weedpacket

Here's a silly question: are you sure you haven't got six duplicate records in the database? The loop is looping (otherwise you wouldn't get six), but the data is the same every time. Either mysql_fetch_array() is broken (unlikely) or your database is wonky (more likely).

tomhath: quoting 'column names' allows embedded spaces.

squidnunc

tomhath: quoting 'column names' allows embedded spaces. [/B][/QUOTE]

I had already removed the spaces from the column names before I read your rseponse above. That wasn't the issue but it doesn't hurt not to have the spaces.

Originally posted by Weedpacket
Here's a silly question: are you sure you haven't got six duplicate records in the database? The loop is looping (otherwise you wouldn't get six), but the data is the same every time. Either mysql_fetch_array() is broken (unlikely) or your database is wonky (more likely).

Weedpacket,

Thanks for your response. I went back to basics to just display all the rows including Casablanca to show you they were different. Guess what? You were right! They are all the same poster with the same title but with different category names so they could be used on various actor and director pages. The different category names is what fooled me. Anyway, thanks for the help.

There are two new issues I am experiencing. First, there are actually 9 different posters with Casablanca in the category name but only one that is named Casablanca. My search using "SELECT * FROM movies WHERE CategoryName='Casablanca' " only returns the poster named Casablanca. How can I make the search more "fuzzy" so it will return posters titled A Night In Casablanca or others with the word Casablanca in them?

Second, my results just run in a single column down the page. I can't for the life of me think of how I can get the results to return in rows of three columns each. Here is my latest version of the code (displaying first 31 rows of database) preceded by a link to the results of the code:

http://www.rhodemap.com/rm/datafeeds/casablanca2.php

<HTML>
<HEAD>
</HEAD>

<BODY BGCOLOR="#FFFFFF">

<?php
/ Connecting, selecting database /
$link = mysql_connect("host", "name", "password")
or die("Could not connect");
mysql_select_db("posters") or die("Could not select database");
/* Performing SQL query */
$query = "SELECT * FROM movies LIMIT 0,30";
$result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */
print "<table width=100%>\n";
    print "\t<tr>\n";
    print "\t\t<td align=center width=33%>\n";
while ($row = mysql_fetch_array($result)) { 
    print "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=".$row{'ProductNumber'}."\"><IMG SRC=\"http://images.allposters.com/images/".$row{'Directory'}."/".$row{'FileName'}."\" WIDTH=".$row{'ThumbWidth'}." HEIGHT=".$row{'ThumbHeight'}." BORDER=0></A>\n";
    print "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=".$row{'ProductNumber'}."\">".$row{'Title'}."</A>\n";
    print "\t<br>".$row{'ProductType'}."\n";
    print "\t<br>".$row{'PosterHeight'}." x ".$row{'PosterWidth'}."<br>\n";
}
print "\t\t</td>\n";
print "\t</tr>\n";
print "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);
?>

</BODY>
</HTML>

Thanks in advance for any suggestions you or anyone else can give me.

Keith

Weedpacket

WHERE Title='%Casablanca%' will match Casablanca, A Night in Casablanca, Casablanca Express etc. (But not Gabriel Over the White House - it's not that smart! 🙂).

ednark

fuzzy sql:

select * from table where column LIKE '%Casablanca%'

% matches anything

so
'%Casablance' means: starts with Casablance

'Casablanca%' means: ends with

[edit]
OOPS:

'%Casablance' means: ends with Casablance

'Casablanca%' means: Starts with

[/edit]

X results per row:

here is my go at it... as my weapon i choose the list()=each() syntax... with this i can make several calls to each after we hit the end of the array and it will not die, it will just return that there are no more items...

this allows me to... Set It And Forget It... we run through once for each column and try and pull out the next arrray item... if the array is 2 items long and we want 10 columns the each() function allows us to make 8 failed each() calls to our array without giving us trouble

<?php
/// set up an array of simple data
$array = array( 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H' );
/// choose how many columns whith which to display
$columns = 3;
/// prepare the table
echo "<table border=1>\n";
/// while we are not at the end of our array
while ( list(,$item) = each($array) ) {
    /// prepare a new row
    echo "\t<tr>\n";
    /// display the item we just pulled out from while() in column 1
    echo "\t\t<td>$item</td>\n";
    /// foreach additional column
    for ( $i=1; $i<$columns; $i++ ) {
        /// if there are any items left
        if ( list(,$item) = each($array) ) {
            /// show the item
            echo "\t\t<td>$item</td>\n";
        /// if are trying to reach beyond the end of the array
        } else {
            /// place a blank
            echo "\t\t<td><br /></td>\n";
        }
    }    

    /// end the row
    echo "\t<tr>\n";
}
/// end the table
echo "</table>\n";
?>

squidnunc

Thanks to Weedpacket and EdNark for your responses. Both of your solutions to the fuzzy search problem were very helpful.

EdNark,

As to the column solution you proposed in such good detail, that looks like more than I dare tackle tonight. I'll play with it tomorrow and maybe some more over the holiday weekend when time allows and post the results when I'm done.

Thanks so much for all the help, guys.

Keith

ednark

hehe sorry... its a little more long winded than it has to be..... it should read much better and be a little more clear if you just take out my comments and run the damned thing....

squidnunc

EdNark,

Please excuse my ignorance - I'm new to all this php stuff. I can't figure out how to plug my code into your code. Here is the code you provided followed by the code I need to plug into it. How does it all fit together?

Your code:

<?php
/// set up an array of simple data
$array = array( 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H' );
/// choose how many columns whith which to display
$columns = 3;
/// prepare the table
echo "<table border=1>\n";
/// while we are not at the end of our array
while ( list(,$item) = each($array) ) {
    /// prepare a new row
    echo "\t<tr>\n";
    /// display the item we just pulled out from while() in column 1
    echo "\t\t<td>$item</td>\n";
    /// foreach additional column
    for ( $i=1; $i<$columns; $i++ ) {
        /// if there are any items left
        if ( list(,$item) = each($array) ) {
            /// show the item
            echo "\t\t<td>$item</td>\n";
        /// if are trying to reach beyond the end of the array
        } else {
            /// place a blank
            echo "\t\t<td><br /></td>\n";
        }
    }    

    /// end the row
    echo "\t<tr>\n";
}
/// end the table
echo "</table>\n";
?>

[/B][/QUOTE]

My code:

<?php
/ Connecting, selecting database /
$link = mysql_connect("host", "name", "pwd")
or die("Could not connect");
mysql_select_db("posters") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT * FROM movies WHERE Title LIKE '%Godfather%' ";
$result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */
print "<table width=100%>\n";
    print "\t<tr>\n";
    print "\t\t<td align=center width=33%>\n";
while ($row = mysql_fetch_array($result)) { 
    print "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=".$row{'ProductNumber'}."\"><IMG SRC=\"http://images.allposters.com/images/".$row{'Directory'}."/".$row{'FileName'}."\" WIDTH=".$row{'ThumbWidth'}." HEIGHT=".$row{'ThumbHeight'}." BORDER=0></A>\n";
    print "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=".$row{'ProductNumber'}."\">".$row{'Title'}."</A>\n";
    print "\t<br>".$row{'ProductType'}."\n";
    print "\t<br>".$row{'PosterHeight'}." x ".$row{'PosterWidth'}."<br>\n";
}
print "\t\t</td>\n";
print "\t</tr>\n";
print "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);

Thanks for all your help.

Keith

ednark

first thing is to replace my $array array with a call to your database... my $array is supposed to mimic your $result

i choose list()=each() because it is the same as effect as

$row = mysql_fetch_array($result);

both return a result if there is one
both return FALSE if there is no more

you can replace each one of my
list()=each()
with your
$row = mysql_fetch_array()

this will not change the flow of my code. once you have gotten the row with your mysql_fetch_array() you should replace my table cell statement :

echo "<td>$item</td>";

with whatever your cell looks like using $row:

echo "<td>{$row['Title']} : {$row['ProductNumber']}</td>";

and you should be good to go

note:
i am unfamiliar with useing $row{'col'} syntax, i thought it was $row['col'] syntax

i know you can use $string{$i} syntax to access and individual character in that position in the string...

does your code actually work like that... if so i would like to remember i can use that syntax

squidnunc

Originally posted by ednark
note:
i am unfamiliar with useing $row{'col'} syntax, i thought it was $row['col'] syntax

i know you can use $string{$i} syntax to access and individual character in that position in the string...

does your code actually work like that... if so i would like to remember i can use that syntax [/B]

ednark,

Thanks so much for your help. I have successfully implemented your code with the additional help you provided in your last post.

Below is the URL to the results page and the code I am using to achieve those results. I used the search "bob" and received 13 results so you can see how it handles the blank cells.

http://www.rhodemap.com/rm/datafeeds/casablanca6.php

<?php
$link = mysql_connect("host", "name", "pwd")
or die("Could not connect");
mysql_select_db("posters") or die("Could not select database");

$query = "SELECT * FROM movies WHERE CategoryName LIKE '%bob%' OR Title LIKE '%bob%' ";
$result = mysql_query($query) or die("Query failed");

$array = array( 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H' );
$columns = 3;
echo "<table align=center width=100% border=0>\n";
while ($row = mysql_fetch_array($result)) { 
    echo "\t<tr>\n";
    echo "\t<td align=center width=33%><br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['ProductNumber']. "\"><IMG SRC=\"http://images.allposters.com/images/" .$row['Directory']. "/" .$row['FileName']. "\" WIDTH=" .$row['ThumbWidth']. " HEIGHT=" .$row['ThumbHeight']. " BORDER=0></A>\n";
    echo "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['ProductNumber']. "\">" .$row['Title']. "</A>\n";
    echo "\t<br>" .$row['ProductType']. "\n";
    echo "\t<br>" .$row['PosterHeight']. " x " .$row['PosterWidth']. "<br></td>\n";
    for ( $i=1; $i<$columns; $i++ ) {
        if ($row = mysql_fetch_array($result)) {
        echo "\t<td align=center width=33%><br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['ProductNumber']. "\"><IMG SRC=\"http://images.allposters.com/images/" .$row['Directory']. "/" .$row['FileName']. "\" WIDTH=" .$row['ThumbWidth']. " HEIGHT=" .$row['ThumbHeight']. " BORDER=0></A>\n";
        echo "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['ProductNumber']. "\">" .$row['Title']. "</A>\n";
        echo "\t<br>" .$row['ProductType']. "\n";
        echo "\t<br>" .$row['PosterHeight']. " x " .$row['PosterWidth']. "<br></td>\n";
        } else {
            echo "\t\t<td><br /></td>\n";
        }
    }    
    echo "\t<tr>\n";
    }
echo "</table>\n";

mysql_free_result($result);

mysql_close($link);

The $row{'col'} syntax seemed to be working fine but I changed it to the $row['col'] syntax you mentioned just in case.

Just out of curiosity and an attempt to learn this stuff better, what does the line $array = array 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H' ); accomplish? I can't even see where the variable "array" is even called again.

Thanks again for your detailed and friendly help.

Keith

ednark

wonderfull.. im glad you got it working.. page looks nice

the $array was there as fake results from your database call...

i did not want to actually make some sql tables... and pull out info from them...

so just i jsut made something to step through as an example...

you can go ahead and kill it now.. you don't need it.

one other optimization... not necessary.. as your code works fine...

use the print <<<SOMETHING syntax.... called heredoc syntax

this allows you to use ' and " without having to worry about \" or \'
and $vars inside will be translated, but its safest to wrap your vars in {} so that php knows what exactly is a variable and what isn't..

so you can rewrite

 echo "\t<td align=center width=33%><br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['ProductNumber']. "\"><IMG SRC=\"http://images.allposters.com/images/" .$row['Directory']. "/" .$row['FileName']. "\" WIDTH=" .$row['ThumbWidth']. " HEIGHT=" .$row['ThumbHeight']. " BORDER=0></A></td>\n";

echo <<SOME_ENDING_DELIMITER

<td align="center"width=33%>
  <br>
  <a href="http://affiliates.allposters.com/link/redirect.asp?aid=781144&item={$row['ProductNumber']}">
  <img src="http://images.allposters.com/images/{$row['Directory']}/{$row['FileName']}"         
       width="{$row['ThumbWidth']}" 
       height="{$row['ThumbHeight']}"       
       border="0">
  </a>
</td>

SOME_ENDING_DELIMITER;

this will retian all formatting.. so that when you view source it will be legible

hopfully the second example should be easier to read.. and therefor easier to maintain... but agian there is nothing incorrect about what you are doing now... and im not sure spending more time rewriting is what you want to do

squidnunc

this will retian all formatting.. so that when you view source it will be legible

ednark,

I tried changed my echos like you said, got into a never-ending series of parse errors, and gave up. When I view the source, it seems readable to me so as long as the code is working, I'm probably gonna leave it be.

I took the job a step further. Many searches returned hundreds of results which would be too much to display on one page. I didn't want to bother you again for a solution so I hunted around and found a solution in the coding of a guestbook tutorial at oxyscripts.com.

Below is the results URL and code including a Previous/Next scheme to break down the pages in sets of 24 results. Let me know what you think.

http://www.rhodemap.com/rm/datafeeds/casablanca7.php

<?php

$link = mysql_connect("host", "name", "pwd")
    or die("Could not connect");
mysql_select_db("posters") or die("Could not select database");

$numresults=mysql_query("SELECT * FROM allposters WHERE artist LIKE '%Rockwell%' "); 
$numrows=mysql_num_rows($numresults) 
or die ("query 1 failed"); 

if (empty($offset)) { 
$offset=0; 
} 

$limit = 24;  

$query = "SELECT * FROM allposters WHERE artist LIKE '%Rockwell%' LIMIT $offset,$limit ";
$result = mysql_query($query) or die("Query failed");

echo "<table width=100% align=center><tr><td align=center>\n";

if ($offset >= 3) { 
$prevoffset = $offset - $limit; 
print "<a href=\"http://www.rhodemap.com/rm/datafeeds/casablanca7.php?offset=$prevoffset\">PREV</a>&nbsp;\n"; 
} 
$pages=intval($numrows/$limit); 
if ($pages < ($numrows/$limit)){ 
$pages=($pages + 1); 
} 
for ($i = 1; $i <= $pages; $i++) { 
$newoffset = $limit*($i-1); 
if ($newoffset == $offset) { 
print "$i&nbsp;\n"; 
} else { 
print "<a href=\"http://www.rhodemap.com/rm/datafeeds/casablanca7.php?offset=$newoffset\">$i</a>&nbsp;\n"; 
} 
} 

//show next if not last 
if (! ( ($offset/$limit) == ($pages - 1) ) && ($pages != 1) ) { 
$newoffset = $offset+$limit; 
print "<a href=\"http://www.rhodemap.com/rm/datafeeds/casablanca7.php?offset=$newoffset\">NEXT</a>\n"; 
}

echo "</td></tr></table>\n";

$columns = 3;
echo "<table align=center width=100% border=0>\n";
while ($row = mysql_fetch_array($result)) { 
    echo "\t<tr>\n";
    echo "\t<td align=center width=33%><br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['prod_num']. "\"><IMG SRC=\"http://images.allposters.com/images/" .$row['directory']. "/" .$row['file_name']. "\" WIDTH=" .$row['thumb_wid']. " HEIGHT=" .$row['thumb_hgt']. " BORDER=0></A>\n";
    echo "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['prod_num']. "\">" .$row['title']. "</A>\n";
    echo "\t<br>" .$row['prod_type']. "\n";
    echo "\t<br>" .$row['prod_hgt']. " x " .$row['prod_wid']. "<br></td>\n";
    for ( $i=1; $i<$columns; $i++ ) {
        if ($row = mysql_fetch_array($result)) {
        echo "\t<td align=center width=33%><br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['prod_num']. "\"><IMG SRC=\"http://images.allposters.com/images/" .$row['directory']. "/" .$row['file_name']. "\" WIDTH=" .$row['thumb_wid']. " HEIGHT=" .$row['thumb_hgt']. " BORDER=0></A>\n";
        echo "\t<br><A HREF=\"http://affiliates.allposters.com/link/redirect.asp?aid=781144&item=" .$row['prod_num']. "\">" .$row['title']. "</A>\n";
        echo "\t<br>" .$row['prod_type']. "\n";
        echo "\t<br>" .$row['prod_hgt']. " x " .$row['prod_wid']. "<br></td>\n";
        } else {
            echo "\t\t<td><br /></td>\n";
        }

    }    
    echo "\t<tr>\n";
    }
echo "</table><p>\n";

echo "<table width=100% align=center><tr><td align=center>\n";

if ($offset >= 3) { 
$prevoffset = $offset - $limit; 
print "<a href=\"http://www.rhodemap.com/rm/datafeeds/casablanca7.php?offset=$prevoffset\">PREV</a>&nbsp;\n"; 
} 
$pages=intval($numrows/$limit); 
if ($pages < ($numrows/$limit)){ 
$pages=($pages + 1); 
} 
for ($i = 1; $i <= $pages; $i++) { 
$newoffset = $limit*($i-1); 
if ($newoffset == $offset) { 
print "$i&nbsp;\n"; 
} else { 
print "<a href=\"http://www.rhodemap.com/rm/datafeeds/casablanca7.php?offset=$newoffset\">$i</a>&nbsp;\n"; 
} 
} 

//show next if not last 
if (! ( ($offset/$limit) == ($pages - 1) ) && ($pages != 1) ) { 
$newoffset = $offset+$limit; 
print "<a href=\"http://www.rhodemap.com/rm/datafeeds/casablanca7.php?offset=$newoffset\">NEXT</a>\n"; 
}

echo "</td></tr></table>\n";

mysql_free_result($result);

mysql_close($link);

I still don't know how a lot of the coding works but I find that I am able to piece things together by finding useful snippets of code on php tutorials and documentation web sites and with the help of people like you. Thanks again.

Keith

squidnunc

ednark,

BTW, I'm heading out of state for Thanksgiving in the morning and won't return until the weekend. So if you answer me late tonight or tomorrow and get no response, it's not because I'm ignoring you.

Have a great Thanksgiving.

Keith

ednark

hey your page looks nice and clean.. your code looks ok ... as long as you can make it do what you want, are good to go...

there are numerous examples of how to do the result spanning you are doing.. so the example you founds looks just like the rest.. perfectly fine...

if your page is going to be a generic search page... then you might also have to pass your search string and sorting order along with your offset on each new pages link ...

looking though other peoples code is one of the best ways to learn...

good luck

PyroX

% matches anything
so
'%Casablance' means: starts with Casablance

'Casablanca%' means: ends with

Maybe I missed it, but isn't it:

text% STARTS WITH 'text'

%text ENDS WITH TEXT

ednark

oops yes... strike that post... reverse it...