Page 1 of 1

Options for displaying DB data

Posted: Fri Aug 16, 2013 3:57 pm
by fletch367
I'm a relative noob when it comes to programming. No educational background but I've been playing with HTML, CSS for about a year now and recently made the jump to PHP a couple months ago. I have some code that I wrote that displays data from a database I created. The code displays the data wonderfully as it is written but I'd like to learn how to give the user options on filtering the data. The DB contains several movie names, images of movie covers and links to the individual movies page at IMDB.com. The code that i've written displays the data in a table that is 4 entries wide and runs until there are no more DB entries. The DB's primary key is "movie_id" and I am using it to sort the display. It worked fine in the beginning b/c movie_id matched up w/an alphabetical list of movie_name. When I added more movie names to the DB the list is no longer alphabetical. If I try to sort by movie_name the display is no longer symetrical. What can I do to have a symetrical display (i.e. 4 entries/row) and sort by "movie_name?" Here is the code:

Code: Select all

	  
	  <?php
$link = mysqli_connect("localhost","xxxx","xxxxx","xxxxx_movies");


if (mysqli_connect_errno()) {
	printf("Connect failed %s\n", mysqli_connect_error());
	exit();
}

$query = "SELECT * FROM Sheet1 ORDER BY movie_id";
$result = mysqli_query($link, $query);

while($row = mysqli_fetch_row($result)) {
  if ($row[0] === 0) {continue;}
  
  elseif ($row[0] % 4 != 0) {
	  echo("
		<td align=\"center\">
		  <a href=\"$row[2]\">$row[1]<p><img src=\"movieCovers/$row[3]\" height=200 width = 150>
		</td>");
	}
  
  else {
    echo ("
	    <td align=\"center\">
		  <a href=\"$row[2]\">$row[1]<p><img src=\"movieCovers/$row[3]\" height=200 width = 150>
		</td>
	  </tr>
	  <tr>");
    }
}
?>

(there is html above and below the above code to open and close table tags. i just didn't want to fill up the page with html)

If anyone has suggestions or resources for me to read please reply. I don't mind, and actually enjoy, figuring out problems on my own but this one has me stumped.

Thanks in advance for all of your help.

fletch

Re: Options for displaying DB data

Posted: Fri Aug 16, 2013 4:47 pm
by Celauran
You could create a form with two selects; one for field and one for order. When the form is submitted (after sanitizing, of course), you use the form data to structure your new query.

Code: Select all

$query = "SELECT field1, field2, etc FROM movies ORDER BY {$order} {$direction}";
Where $order and $direction are your validated, sanitized form inputs. Once you're comfortable with that, you can move on to adding fields for a WHERE clause, results per page, and then have the whole thing done asynchronously.

Re: Options for displaying DB data

Posted: Fri Aug 16, 2013 4:50 pm
by Celauran
A few other notes:
I find PDO to be much more pleasant to work with than MySQLi (in addition to being portable)
Use prepared statements
Avoid "SELECT *". Specify which columns you want returned.

Re: Options for displaying DB data

Posted: Fri Aug 16, 2013 6:16 pm
by fletch367
I'm sorry but I don't understand your reply in relation to my question. Maybe I asked the question wrong or maybe i just don't understand.

I'm looking to display my data in the following format:

x x x x
x x x x
x x x x
x x x x
.......
(where each x is movie_name and movie_image and every row has 4 entries)

Using my while loop and ORDER BY movie_id i get the above format but if I ORDER BY movie_name i get the following"

x x x x x
x x
x x
x x x
x
x x x x
x x x x x x x
.......

I understand that it's doing this because I'm using movie_id % 4 == 0. So if the second item displayed has the ID that is a multiple of 4 my code will create a new row. Then if the next item is a multiple of 4 it will again create a new row.

What I'm trying to figure out is how to get each row of displayed data to be the same amount. Am I going to have to reassign new ID #s every time I add a movie to the database? How else could I code this to get the desired results?

fletch

Re: Options for displaying DB data

Posted: Fri Aug 16, 2013 7:50 pm
by Celauran
Sorry, I missed the layout part of your initial question. There's no need to use id % 4; you can just float the elements. Alternately, if you want to lay things out in a table 4 columns wide, just use a counter as you iterate over the database results and create the new row when its %4 == 0.

Re: Options for displaying DB data

Posted: Sat Aug 17, 2013 6:17 pm
by fletch367
GOT IT!! That was definitely one of those derrrrr moments. Hopefully everyone has experienced these. You know the ones where the answer is so simple but you've looked at the problem so long you just can't see it. That's why these forums are so great!!

Thank you very much for the help!!

fletch