Advert system trouble

[middleman666]

hey people,

I'm having trouble getting my head round an advert system im trying to make.

I have a number of rows in a table that have the following field names:
id
Page
Artist
TopImage

I want to be able to display the TopImage depending on the Page and Artist

The page name and artist name will be set by variables in the URL, for example:
...domain.com/index.php?page=review&artist=thewho

What I want to say is:
If the rows Page is equal to review and artistis equal to thewho then dsplay the TopImage, but if the Page is equal but the artist isnt then randomly pick a row where the page is equal to the page in the url, but if the artist is equal and the page isnt then randomly pick a row that has that artist. And, if none are set then randomaly select a row that has TopImage set.

Sorry if thats a bit hard to understand.

I have tried making it, but soon realised I was going about it the wrong way.

heres what I have:

<?php
include('dbconnect.php');
$TableName = 'Adverts';
$addir = 'Adverts/';
$page= $_GET['page'];
if (empty($page)){ $page = 'home'; }
$artist = $_GET['artist'];

// Build SQL Query  

$query = "select * from $TableName order by id DESC";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative
if ($numrows == 0) {
  echo 'There are no entries yet...';
}

//set up layout
$l1 = '<div align="center"><a href="';
$l2 = '" target="_blank"><span class="adimages"><img src="';
$l3 = '" border="0"></span></a></div>';

// get results
$result = mysql_query($query) or die("Couldn't execute query");

// now display the results returned
while ($row= mysql_fetch_array($result)) {

  //Top Advert
  if (!empty($row['TopImage'])){
    if ($row['Page'] == $page){
      if ($row['Artist'] == $artist){
        $TopAdvert = $l1.$row['TopLink'].$l2.$addir.$row['TopImage'].$l3;
      }
      else {
	    //Pick a random ad where page is equal to menu name
        $TopAdvert = $l1.$row['TopLink'].$l2.$addir.$row['TopImage'].$l3;
      }
	}
	else {
	  //Page isn't equal to menu name
	  if ($row['Artist'] == $artist){
        $TopAdvert = $l1.$row['TopLink'].$l2.$addir.$row['TopImage'].$l3;
      }
      else {
	    //Pick a random ad where TopImage is set
	    $TopAdvert = $l1.$row['TopLink'].$l2.$addir.$row['TopImage'].$l3;
      }
	}
  }
  else {
    //There is no TopImage,
    echo '';
  }

}
?>

As you can see, this would only apply to the first row so I'm thinking I would need to add new queries within the while function where it would say wheather the page was equal to Page etc. then if a result was found then go on to the next query.

What I can't get my head around is first, am I asking the right questions in the correct order? and secondly, would my method actualy work?

Thanks for any help.

[bpat1434]

Well, you'd want to do it all in one query, it's much faster and less memory intensive. The only thing you really need to do, is make sure your queries are set up right. Something like the following would probably work perfectly for you:

<?php
$table = 'Adverts';
$addir = 'Adverts/';
// Used in every query, regardless of page or artist
$query = "
	SELECT TopImage, TopLink
	FROM {$table}";

# Find exact Page & Artist
if((isset($_GET['page']) && !empty($_GET['page'])) && (isset($_GET['artist']) && !empty($_GET['artist'])))
  $query .= "
	WHERE page='" . $_GET['page'] . "' 
		AND artist='" . $_GET['artist'] . "'";
# We have a page, but no artist
elseif((isset($_GET['page']) && !empty($_GET['page'])) && (!isset($_GET['artist']) || empty($_GET['artist'])))
  $query = "
	WHERE page='". $_GET['page']. "'
	ORDER BY RAND()";
# We have an artist, but no page
elseif((!isset($_GET['page']) || empty($_GET['page'])) && (isset($_GET['artist']) && !empty($_GET['artist'])))
  $query = "
	WHERE artist='". $_GET['artist']. "'
	ORDER BY RAND()";
# Nothing must be set, or they're all empty!!
else
  $query = "
	ORDER BY RAND()";

// Limit it to one row
$query .= "
	LIMIT 0,1";

$result = mysql_query($query);

if(!$result)
	die('There are no entries yet . . .');

//set up layout
$l1 = '<div align="center"><a href="';
$l2 = '" target="_blank"><span class="adimages"><img src="';
$l3 = '" border="0"></span></a></div>';

$row = mysql_fetch_assoc($result); // Only one row, no need for a loop ;)
mysql_free_result($result);

echo $l1 . $row['TopLink'] . $l2 . $addir . $row['TopImage'] . $l3;
?>

The if()elseif()elseif()else{} "switch" statement goes through and assess all the permutations there can be. Based upon your requirements:
1.) If page and artist set, get that exact row
2.) If page is set, and artist is not, get a random row from within "page"
3.) If artist is set, and page is not, get a random row from within "artist"
4.) Otherwise, just grab a random row
You'll see in my comments that's just what I did. Hope you can see how your queries differ from mine. You just needed some "WHERE" clauses and a few "ORDER BY" with some "LIMIT" clauses to get what you need.

[middleman666]

hey,

Thanks for the reply.

I had to change some of your code to work with variables I already have in place, the ones I posted, I renamed as I thought it would make it a bit more understandable.

Anyway, I changed it to this:

<?php
include('dbconnect.php');

$page = $_GET['menu'];
if (empty($page)){ $page = 'home'; }

$id = $_GET['id'];

$artist = 'thewho';

$table = 'Adverts'; 
$addir = 'Adverts/'; 

// Used in every query, regardless of page or artist 
$query = " 
    SELECT TopImage, TopLink 
    FROM {$table}"; 

// Find exact Page & Artist 
if ((isset($page) && !empty($page)) && (isset($artist) && !empty($artist))) 
  $query .= " 
    WHERE Page='" . $page . "' 
    AND Artist='" . $artist . "'"; 

// We have a page, but no artist 
else if ((isset($page) && !empty($page)) && (!isset($artist) || empty($artist))) 
  $query = " 
    WHERE Page='". $page . "' 
    ORDER BY RAND()"; 

// We have an artist, but no page 
else if ((!isset($page) || empty($page)) && (isset($artist) && !empty($artist))) 
  $query = " 
    WHERE Artist='". $artist . "' 
    ORDER BY RAND()"; 

// Nothing must be set, or they're all empty!! 
else 
  $query = " 
    ORDER BY RAND()"; 

// Limit it to one row 
$query .= " 
    LIMIT 0,1"; 

$result = mysql_query($query); 

if(!$result) 
    die('There are no entries yet . . .'); 

//set up layout 
$l1 = '<div align="center"><a href="'; 
$l2 = '" target="_blank"><span class="adimages"><img src="'; 
$l3 = '" border="0"></span></a></div>'; 

$row = mysql_fetch_assoc($result); // Only one row, no need for a loop ;) 
mysql_free_result($result); 

$TopAdvert = $l1 . $row['TopLink'] . $l2 . $addir . $row['TopImage'] . $l3; 
?> 

But now all that outputs is the img tag with no src (TopImage) and no link (TopLink).

I thought what I changed would be ok, but have I done something to cause it not to work properly?

Thanks again for your help.

[bpat1434]

Try adding an "or die(mysql_error());" to the mysql_query call:

$result = mysql_query($query) or die(mysql_error());

What's that tell you now?

Or perhaps commenting out the mysql_free_result call, or even moving it to after the $TopImage = $l1.... line.

[middleman666]

It doesn't return any mysql_error.
I tried commenting out the mysql_free_result and then moving it and uncommenting it, but I still get the same result.

Just a blank img tag.

[bpat1434]

Okay, change the SELECT statement to:

SELECT *

[middleman666]

Still getting the same :S

[bpat1434]

what does your table look like? Like how is it set up?

[middleman666]

I used all text types:

create table Adverts
(
id int not null primary key auto_increment,
Artist text,
Page text,
TopImage text,
TopLink text
);

Thanks for helping.

[bpat1434]

Try running the query manually to see what you get. It may not be returning a result set, but I'm not sure why. I'd have to get access to the db for that. But if you run the query through something like phpMyAdmin, and see what the result is, let me know and we'll go from there.

[middleman666]

The 3 rows I have are:
shown in this field order: id, Page, Artist, TopImage, TopLink
1 | home | thewho | thewho-top.jpg | [url]http://...etc[/url].
2 | home | notartist | this-top.jpg | [url]http://...etc[/url].
3 | reviews | notartist | new-top.jpg | [url]http://...etc[/url].

SELECT * FROM `Adverts` WHERE `Artist` = 'thewho' ORDER BY RAND() LIMIT 0,1

Returned one result

SELECT * FROM `Adverts` WHERE `Page` = 'home' AND `Artist` = 'thewho' ORDER BY RAND() LIMIT 0,1

For some reason this didn't work, just took me to 'page cannot be displayed'

SELECT * FROM `Adverts` WHERE `Page` = 'home' ORDER BY RAND() LIMIT 0,1

Returned one result

SELECT * FROM `Adverts` LIMIT 0,1

Again, this just kept taking me to a 'page cannot be displayed' page.

[middleman666]

Right, I think I know whats going on.

I've been echoing the query line and I've seen it come up with this:

SELECT * FROM Adverts WHERE Page='showreview' AND Artist='thewho' LIMIT 0,1 

Trouble is, its not displaying thewho advert because its asking for the rows Page to be equal to showreview and the rows page is equal to home.

Seen as the $_GET['menu'] is always going to set to something except on the homepage, I need the query to say if an entry cant be found with both the page and artist set, then try and find a result where just the artist is set and if no row is found, then just display a random row.

Should that be a new query added to the end of the script?

[middleman666]

I made it look pretty ugly at the bottom but it seems to be working:

<?php
include('dbconnect.php');

$page = $_GET['menu'];
if (empty($page)){ $page = ''; }

$id = $_GET['id'];
if (!empty($id)) {
  $artistquery = "SELECT Artist FROM Reviews WHERE id = '$id' LIMIT 1";
  $artistresult = mysql_query($artistquery) or die(mysql_error()); 
  $artistrow = mysql_fetch_assoc($artistresult);

  $artist = $artistrow['Artist'];
}
else {
  $artist = '';
}

$table = 'Adverts'; 
$addir = 'Adverts/'; 

// Used in every query, regardless of page or artist 
$query = " 
    SELECT *  

    FROM {$table}"; 

// Find exact Page & Artist 
if ((isset($page) && !empty($page)) && (isset($artist) && !empty($artist))) 
  $query .= " 
    WHERE Page='" . $page . "'
    AND Artist='" . $artist . "'"; 

// We have a page, but no artist 
else if ((isset($page) && !empty($page)) && (!isset($artist) || empty($artist))) 
  $query .= " 
    WHERE Page='". $page . "'
    ORDER BY RAND()"; 

// We have an artist, but no page 
else if ((!isset($page) || empty($page)) && (isset($artist) && !empty($artist))) 
  $query .= " 
    WHERE Artist='". $artist . "' 
    ORDER BY RAND()"; 

// Nothing must be set, or they're all empty!! 
else 
  $query .= " 
    ORDER BY RAND()"; 

// Limit it to one row 
$query .= " 
    LIMIT 0,1"; 

//set up layout 
$l1 = '<div align="center"><a href="'; 
$l2 = '" target="_blank"><span class="adimages"><img src="'; 
$l3 = '" border="0"></span></a></div>'; 

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

//If we have no results
if ($numrows == 0) {
  $artistquery = "SELECT * FROM $table ORDER BY RAND() LIMIT 1";
  $artistresult = mysql_query($artistquery) or die(mysql_error()); 
  $artistrow = mysql_fetch_assoc($artistresult);

  $TopAdvert = $l1 . $artistrow['TopLink'] . $l2 . $addir . $artistrow['TopImage'] . $l3; 
}
else {

  $result = mysql_query($query) or die(mysql_error()); 

  if(!$result) 
    die('There are no entries yet . . .'); 

  $row = mysql_fetch_assoc($result); // Only one row, no need for a loop ;) 
  mysql_free_result($result); 

  $TopAdvert = $l1 . $row['TopLink'] . $l2 . $addir . $row['TopImage'] . $l3; 
}
?> 

Is there a way to neated up that last IF function or make it all into one query again instead of having that last one at the bottom?

Thanks.

EDIT: just noticed its not working correctly, it doesnt work when the artist is set, looks like id have to add another if function within the one checking to see if a result was there. :S

[bpat1434]

Okay, I've set up my table to mimick yours, and using the following code, it works (well, would if I had the images 😉)

<?php

$qpage = (!isset($_GET['menu']) || empty($_GET['menu'])) ? '' : mysql_real_escape_string($_GET['menu']);
$qartist = (!isset($_GET['artist']) || empty($_GET['artist'])) ? '' : mysql_real_escape_string($_GET['artist']);
$page = empty($qpage) ? 'home' : $qpage;

$table = 'Adverts';
$addir = 'Adverts/';
// Used in every query, regardless of page or artist
$query = "
    SELECT TopImage, TopLink
    FROM {$table}";

if(!empty($qpage) && !empty($qartist))
  $query .= "
    WHERE page='" . $qpage . "'
        AND artist='" . $qartist . "'";
elseif(!empty($qpage) && empty($qartist))
  $query .= "
    WHERE page='". $qpage. "'
    ORDER BY RAND()";
elseif(empty($qpage) && !empty($qartist))
  $query .= "
    WHERE artist='". $qartist. "'
    ORDER BY RAND()";
else
  $query .= "
    ORDER BY RAND()";


$query .= "
    LIMIT 0,1";

echo nl2br($query);

$result = mysql_query($query);

if(!$result || mysql_num_rows($result) == 0)
    die('There are no entries yet . . .');

//set up layout
$l1 = '<div align="center"><a href="';
$l2 = '" target="_blank"><span class="adimages"><img src="';
$l3 = '" border="0"></span></a></div>';

$row = mysql_fetch_assoc($result); // Only one row, no need for a loop ;)
var_dump($row);

echo $l1 . $row['TopLink'] . $l2 . $addir . $row['TopImage'] . $l3;
?>

Now, obviously that only works if your menu is set to "home" or "reviews" or whatever it is you set that column to, and the same thing for the artist.

As I said, the above works in all instances, NO artist And NO menu, Artist with NO menu, Menu with NO artist, Menu with Artist.

[middleman666]

Cheers for the help.
I tried your code, tweeked it a bit to read the $page and $artist variables I set up last time, but I couldn't seem to get it to work.

It basically kept saying it couldn't find any results for some reason.

Before you posted your last code, I had been tweeking round with what you first gave me and my ugly coding and I've got it to work the way I want it.

Only set back is my ugly coding: (sorry if its a bit hard to make out)

<?php
include('dbconnect.php');

$page = $_GET['menu'];
if (empty($page)){ $page = ''; }

$id = $_GET['id'];
if (!empty($id)) {
  $artistquery = "SELECT Artist FROM Reviews WHERE id = '$id' LIMIT 1";
  $artistresult = mysql_query($artistquery) or die(mysql_error()); 
  $artistrow = mysql_fetch_assoc($artistresult);

  $artist = str_replace("'", "\'",$artistrow['Artist']);

}
else {
  $artist = '';
}

$table = 'Adverts'; 
$addir = 'Adverts/'; 

// Used in every query, regardless of page or artist 
$query = " 
    SELECT *  

    FROM {$table}"; 

// Find exact Page & Artist 
if ((isset($page) && !empty($page)) && (isset($artist) && !empty($artist))) 
  $query .= " 
    WHERE Page='" . $page . "'
    AND Artist='" . $artist . "'"; 

// We have a page, but no artist 
else if ((isset($page) && !empty($page)) && (!isset($artist) || empty($artist))) 
  $query .= " 
    WHERE Page='". $page . "'
    ORDER BY RAND()"; 

// We have an artist, but no page 
else if ((!isset($page) || empty($page)) && (isset($artist) && !empty($artist))) 
  $query .= " 
    WHERE Artist='". $artist . "' 
    ORDER BY RAND()"; 

// Nothing must be set, or they're all empty!! 
else 
  $query .= " 
    ORDER BY RAND()"; 

// Limit it to one row 
$query .= " 
    LIMIT 0,1"; 

//set up layout 
$l1 = '<div align="center"><a href="'; 
$l2 = '" target="_blank"><span class="adimages"><img src="'; 
$l3 = '" border="0"></span></a></div>'; 

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

//If we have no results
if ($numrows == 0 && isset($artist) && !empty($artist)) {
  $artistquery = "SELECT * FROM $table WHERE Artist = '$artist' ORDER BY RAND() LIMIT 1";

  $numartistresults=mysql_query($artistquery);
  $numartistrows=mysql_num_rows($numartistresults);

  $artistresult = mysql_query($artistquery) or die(mysql_error()); 
  $artistrow = mysql_fetch_assoc($artistresult);

  if (!empty($artistrow['TopImage'])){ $TopAdvert = $l1 . $artistrow['TopLink'] . $l2 . $addir . $artistrow['TopImage'] . $l3; }
  if (!empty($artistrow['BottomImage'])){ $BottomAdvert = '<br>'.$l1 . $artistrow['BottomLink'] . $l2 . $addir . $artistrow['BottomImage'] . $l3; }
  if (!empty($artistrow['LeftImage'])){ $LeftAdvert = $l1 . $artistrow['LeftLink'] . $l2 . $addir . $artistrow['LeftImage'] . $l3; }
  if (!empty($artistrow['RightImage'])){ $RightAdvert = $l1 . $artistrow['RightLink'] . $l2 . $addir . $artistrow['RightImage'] . $l3; }

  if ($numartistrows == 0) {
    $randquery = "SELECT * FROM $table ORDER BY RAND() LIMIT 1";

$randresult = mysql_query($randquery) or die(mysql_error()); 
$randrow = mysql_fetch_assoc($randresult);

if (!empty($randrow['TopImage'])){ $TopAdvert = $l1 . $randrow['TopLink'] . $l2 . $addir . $randrow['TopImage'] . $l3; }
if (!empty($randrow['BottomImage'])){ $BottomAdvert = '<br>'.$l1 . $randrow['BottomLink'] . $l2 . $addir . $randrow['BottomImage'] . $l3; }
if (!empty($randrow['LeftImage'])){ $LeftAdvert = $l1 . $randrow['LeftLink'] . $l2 . $addir . $randrow['LeftImage'] . $l3; }
if (!empty($randrow['RightImage'])){ $RightAdvert = $l1 . $randrow['RightLink'] . $l2 . $addir . $randrow['RightImage'] . $l3; }
  }

}
//If we have no results
else if ($numrows == 0 && isset($page) && !empty($page)) {
  $pagequery = "SELECT * FROM $table WHERE Page = '$page' ORDER BY RAND() LIMIT 1";

  $numpageresults=mysql_query($pagequery);
  $numpagerows=mysql_num_rows($numpageresults);

  $pageresult = mysql_query($pagequery) or die(mysql_error()); 
  $pagerow = mysql_fetch_assoc($pageresult);

  if (!empty($pagerow['TopImage'])){ $TopAdvert = $l1 . $pagerow['TopLink'] . $l2 . $addir . $pagerow['TopImage'] . $l3; }
  if (!empty($pagerow['BottomImage'])){ $BottomAdvert = '<br>'.$l1 . $pagerow['BottomLink'] . $l2 . $addir . $pagerow['BottomImage'] . $l3; }
  if (!empty($pagerow['LeftImage'])){ $LeftAdvert = $l1 . $pagerow['LeftLink'] . $l2 . $addir . $pagerow['LeftImage'] . $l3; }
  if (!empty($pagerow['RightImage'])){ $RightAdvert = $l1 . $pagerow['RightLink'] . $l2 . $addir . $pagerow['RightImage'] . $l3; }

  if ($numpagerows == 0) {
    $randquery = "SELECT * FROM $table ORDER BY RAND() LIMIT 1";

$randresult = mysql_query($randquery) or die(mysql_error()); 
$randrow = mysql_fetch_assoc($randresult);

if (!empty($randrow['TopImage'])){ $TopAdvert = $l1 . $randrow['TopLink'] . $l2 . $addir . $randrow['TopImage'] . $l3; }
if (!empty($randrow['BottomImage'])){ $BottomAdvert = '<br>'.$l1 . $randrow['BottomLink'] . $l2 . $addir . $randrow['BottomImage'] . $l3; }
if (!empty($randrow['LeftImage'])){ $LeftAdvert = $l1 . $randrow['LeftLink'] . $l2 . $addir . $randrow['LeftImage'] . $l3; }
if (!empty($randrow['RightImage'])){ $RightAdvert = $l1 . $randrow['RightLink'] . $l2 . $addir . $randrow['RightImage'] . $l3; }
  }

}
else {

  $result = mysql_query($query) or die(mysql_error()); 

  if(!$result) 
    die('There are no entries yet . . .'); 

  $row = mysql_fetch_assoc($result); // Only one row, no need for a loop ;) 
  mysql_free_result($result); 

  if (!empty($row['TopImage'])){ $TopAdvert = $l1 . $row['TopLink'] . $l2 . $addir . $row['TopImage'] . $l3; }
  if (!empty($row['BottomImage'])){ $BottomAdvert = '<br>'.$l1 . $row['BottomLink'] . $l2 . $addir . $row['BottomImage'] . $l3; }
  if (!empty($row['LeftImage'])){ $LeftAdvert = $l1 . $row['LeftLink'] . $l2 . $addir . $row['LeftImage'] . $l3; }
  if (!empty($row['RightImage'])){ $RightAdvert = $l1 . $row['RightLink'] . $l2 . $addir . $row['RightImage'] . $l3; }
}
?> 

As you can see, I've added a bottom advert, left advert and right advert.

So this is what is happening:
- If theres no artist and no page then a random advert is displayed
- If theres an artist but no page then if theres a row/s with that artist then it is displayed.
- If theres no artist but a page set then if theres a row/s with that page then it is displayed.
- if there isnt a row which matches either artist or page then a random advert is displayed.

I'm also now saying that if an image isnt set for the adverts that want to be displayed then not to display anything. - it may be better to say if an advert isnt set then dispaly a random one where it is set, but that could lead to more of my ugly coding :S