asked on

Random selection from DB not working correctly

Hi everyone,

I have a script where users can tick between 1-6 different checkboxes (each checkbox is based on a different category id in the DB) and then a random record is displayed from the DB based on what the user selected. From example if the user ticks checkboxes 1 and 2, the script should randomly select a record from the DB with either a catagory id of 1 or 2.

For some reason it is not selecting records from the DB with a gen_enq_cat id of 1,3 or 5 and the others work correctly? I think it is how the counter has been setup but I would really appreciate it if someone could have a look at this.

// Grabbing the filters

	$filters = array();

	if ( $_POST["checkbox1"] != NULL )
	{
			array_push($filters,$_POST["checkbox1"]);
	}
	if ( $_POST["checkbox2"] != NULL )
	{
			array_push($filters,$_POST["checkbox2"]);
	}
	if ( $_POST["checkbox3"] != NULL )
	{
			array_push($filters,$_POST["checkbox3"]);
	}
	if ( $_POST["checkbox4"] != NULL )
	{
			array_push($filters,$_POST["checkbox4"]);
	}
	if ( $_POST["checkbox5"] != NULL )
	{
			array_push($filters,$_POST["checkbox5"]);
	}
	if ( $_POST["checkbox6"] != NULL )
	{
			array_push($filters,$_POST["checkbox6"]);
	}

// Showing a random enquiry

	$condition = true;
	$counter = 0;

	while ( $condition )
	{
		// Picking a random enquiry from the enquiries table
		$query = mysql_query("SELECT * FROM gen_enquiry_table ORDER BY RAND() LIMIT 1") or die ( mysql_error() );
		
		// Storing the content of the selected row (if one exists) in an array

		if ( mysql_num_rows($query) > 0 )
		{
			$row = mysql_fetch_array($query);
			$enquiry_html = $row["gen_enq_txt"];
			$enquiry_desc = $row["gen_enq_desc"];
			$enquiry_cat = $row["gen_enq_cat"];
			
			// Outputting the entire HTML code of the selected enquiry stored in the database, if it matches the selected filters
			if ( in_array($enquiry_cat,$filters) )
			{
				echo $enquiry_desc;
				echo $enquiry_html;
				$condition = false; // breaking outa of the loop
			}
			else
			{
				$counter += 1; // increasing the counter variable by one
				if ( $counter > 6 )
				{
					echo "The enquires table seems to be empty! Please try again with different filters!";
					$condition = false;
				}
			}
		
		}
		else
		{
			echo "The enquires table seems to be empty! Please try again later!";
			$condition = false; // breaking outa of the loop
			exit();
		}
	}

}
else
{
	?>

Open in new window

Here is how the checkboxes are being displayed:

<li class="list">
      <img src="images/hakomi.jpg" height="100" width="120" />
      <label class="filter">H
      <input type="checkbox" name="checkbox1" value="2" checked/>
      </label>
 	  </li>

Open in new window

Robert Schutt

You seem to be assuming that if you select 6 random records, one of them will have one of the chosen categories. This doesn't have to be true, in which case the same error will be shown as when no records exist with one of those categories.

You should print out each of the selected records to be sure:
1) they are in fact chosen randomly;
2) to which category they belong, this will make sure the logic is not at fault.

By the way, I would do this a bit differently: you could change the query to include something like: WHERE gen_enq_cat IN (1, 2)

TLN_CANADA

ASKER

Thanks Robert, I will check this out and get back to you.

Ray Paseur

First, let's deconstruct this statement:

$query = mysql_query("SELECT * FROM gen_enquiry_table ORDER BY RAND() LIMIT 1") or die ( mysql_error() );

As a general rule you would want to create the query string in a separate variable like this, so you can see the content of the failing query:

$sql = "SELECT * FROM gen_enquiry_table ORDER BY RAND() LIMIT 1";
$res = mysql_query($sql) or die ( "FAIL: $sql BECAUSE: " . mysql_error() );

In this case you know what the query string contains, but when you have a dynamically created query string you really want to be able to see the content of the variables as they are rendered inside the query string.

To the question of how you might use checkboxes in a PHP form, this article shows some ideas that have worked well for me.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_5450-Common-Sense-Examples-Using-Checkboxes-with-HTML-JavaScript-and-PHP.html

Checkboxes play well with HTML arrays. Try running this to see what I mean. For a web-facing application you would probably want to add some sanity checks, but this will illustrate the essential moving parts.

<?php // RAY_temp_tln_canada.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// POSSIBLE VALUES FOR THE CHECKBOXES
$numbers = range(1,6);

// IF ANTYHING WAS SUBMITTED
if (!empty($_GET['cbox']))
{
    // CREATE THE 'IN' AND 'WHERE' CLAUSES FOR THE QUERY
    $in = '(' . implode(',', $_GET['cbox']) . ')';
    $wh = 'WHERE column_name IN ' . $in;
    
    // SHOW THE CLAUSES
    var_dump($wh);
}

// GENERATE THE CHECKBOXES
$cb = NULL;
foreach ($numbers as $n)
{
    $cb .= '<input name="cbox[]" type="checkbox" value="' . $n . '" />' . $n . PHP_EOL;
}

// CREATE THE FORM
$form = <<<EOD
<form>
Check some boxes:<br/>
$cb
<input type="submit" value="Submit Your Selection" />
</form>
EOD;
echo $form;

Open in new window

TLN_CANADA

ASKER

Thank you Ray for your advice about the separate string variable. I have changed this and will implement it in the future also. As I won't have more than 6 checkboxes on the screen, I don't think it's necessary to call them in an array based on what is in the DB (I have images hardcoded on the page etc also)

Robert, I changed the query to include the WHERE clause you mentioned. I've tested it a lot more this morning and I think I know what part is causing the issue. If I leave all of the checkboxes ticked it will correctly select a random record from the DB from all the catagories. However, if I just select checkboxes with an id of 1 or 3 or 5 it does not display records with these catagory id and gives the return : "The enquires table seems to be empty! Please try again with different filters!"

(The value in the checkbox field corresponds to the catagory id and I have verified that these are correct)

Here is what I have on the page now, (it could also be the way the last else loop is setup that is causing this issue):

<?php

if (isset($_POST["Submit"]))
{

// Connecting to the MySQL database now

	mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );

// Selecting the database

	mysql_select_db('foxdbmain') or die ( mysql_error() );

// Grabbing the filters

	$filters = array();

	if ( $_POST["checkbox1"] != NULL )
	{
			array_push($filters,$_POST["checkbox1"]);
	}
	if ( $_POST["checkbox2"] != NULL )
	{
			array_push($filters,$_POST["checkbox2"]);
	}
	if ( $_POST["checkbox3"] != NULL )
	{
			array_push($filters,$_POST["checkbox3"]);
	}
	if ( $_POST["checkbox4"] != NULL )
	{
			array_push($filters,$_POST["checkbox4"]);
	}
	if ( $_POST["checkbox5"] != NULL )
	{
			array_push($filters,$_POST["checkbox5"]);
	}
	if ( $_POST["checkbox6"] != NULL )
	{
			array_push($filters,$_POST["checkbox6"]);
	}

// Showing a random enquiry

	$condition = true;
	$counter = 0;

	while ( $condition )
	{
		// Picking a random enquiry from the enquiries table
		$sql = "SELECT * FROM gen_enquiry_table WHERE gen_enq_cat IN (1, 2, 3, 4, 5, 6) ORDER BY RAND() LIMIT 1";
		$query = mysql_query($sql) or die ( "FAIL: $sql BECAUSE: " . mysql_error() );
		
		echo $query ;
		
		// Storing the content of the selected row (if one exists) in an array

		if ( mysql_num_rows($query) > 0 )
		{
			$row = mysql_fetch_array($query);
			$enquiry_html = $row["gen_enq_txt"];
			$enquiry_desc = $row["gen_enq_desc"];
			$enquiry_cat = $row["gen_enq_cat"];
			
			// Outputting the entire HTML code of the selected enquiry stored in the database, if it matches the selected filters
			if ( in_array($enquiry_cat,$filters) )
			{
				echo $enquiry_desc;
				echo $enquiry_html;
				$condition = false; // breaking outa of the loop
			}
			else
			{
				$counter += 1; // increasing the counter variable by one
				if ( $counter > 6 )
				{
					echo "The enquires table seems to be empty! Please try again with different filters!";
					$condition = false;
				}
			}
		
		}
		else
		{
			echo "The enquires table seems to be empty! Please try again later!";
			$condition = false; // breaking outa of the loop
			exit();
		}
	}

}
else
{
	?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<script>
window.onload = function() {
    document.getElementById("checky").onchange = function() {
        setAllCheckboxes(this.checked);
        document.getElementById("forchecky").innerHTML = this.checked?"Uncheck all":"Check all";
    }
}
function setAllCheckboxes(state) {
    var inputs = document.getElementsByTagName("input");
    for(var i=0;i<inputs.length;i++)  if(inputs[i].name && /checkbox\d/.test(inputs[i].name)) inputs[i].checked = state;
}

</script>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href='http://fonts.googleapis.com/css?family=Oswald' rel='stylesheet' type='text/css' />
<title>Untitled Document</title>
</head>
<style>
label.filter {
font-size:12px;
padding-left:5px;
}
li.list {
float:left;
width:120px;
-webkit-border-radius: 0px 0px 2px 2px;
border-radius: 0px 0px 2px 2px;
margin:0px 20px 20px 0px;
border:1px solid #0066cc;
background-color:#3FA9F5;
}
</style>

<body style="color:#555555;
	line-height:1.5em;
	font-family:'Lucida Grande';">
  
  
<!--Master div adds 20px padding -->
<div style="width:750px;margin:40px;background: rgb(255,255,255); /* Old browsers */
background: -moz-linear-gradient(top, rgba(255,255,255,1) 25%, rgba(245,249,240,1) 61%, rgba(207,207,211,1) 93%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(25%,rgba(255,255,255,1)), color-stop(61%,rgba(245,249,240,1)), color-stop(93%,rgba(207,207,211,1))); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, rgba(255,255,255,1) 25%,rgba(245,249,240,1) 61%,rgba(207,207,211,1) 93%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, rgba(255,255,255,1) 25%,rgba(245,249,240,1) 61%,rgba(207,207,211,1) 93%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, rgba(255,255,255,1) 25%,rgba(245,249,240,1) 61%,rgba(207,207,211,1) 93%); /* IE10+ */
background: linear-gradient(to bottom, rgba(255,255,255,1) 25%,rgba(245,249,240,1) 61%,rgba(207,207,211,1) 93%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#ffffff', endColorstr='#cfcfd3',GradientType=0 ); /* IE6-9 */ -webkit-border-radius: 8px;
      border-radius: 8px;
      -webkit-box-shadow: 0px 0px 20px 10px #555;
      box-shadow: 0px 0px 20px 10px #555;
      border:2px solid #444;">
      
  <!--Add headline text -->
<h1 style="
    color:#3FA9F5;
	font-family: 'Oswald', Arial, sans-serif;
	font-weight:normal;
	font-size:34px;
	text-shadow:1px 1px 1px #0066cc;
	letter-spacing:1px;
    margin-left:30px;
    margin-top:40px;
">Generate Enquiry</h1>    
      
      
 
   <p style="font-family: 'Oswald', Arial, sans-serif;color:#555;padding:10px 0 10px 30px;font-size:16px;width:600px;">On this page you can have the system ask you series of questions, in a particular tradition or style. You can also filter by category. Please select the categories you wish to filter the enquiries by:</p>
   
  <!--Main content -->
<form method="post" name="form1" class="style4" id="form1">
<label for="checky" id="forchecky" style="font-family: 'Oswald', Arial, sans-serif;padding:10px 0 10px 30px;color:#555;font-size:16px;width:600px;">Uncheck all</label><input type="checkbox" checked="checked" id="checky" />

<!--Created list of items -->
<ul style="list-style-type:none;margin-left:30px;padding:0;height:270px;width:450px;">
<!--styled list items with class -->
      <li class="list">
      <img src="images/hak.jpg" height="100" width="120" />
      <label class="filter">Hak
      <input type="checkbox" name="checkbox1" value="2" checked/>
      </label>
 	  </li>  
      <li class="list">
      <img src="images/ges.jpg" height="100" width="120" style="" />
      <label class="filter">Ges
      <input type="checkbox" name="checkbox2" value="4" checked/>
      </label>
      </li>
      <li class="list">
      <img src="images/ze.jpg" height="100" width="120" />
      <label class="filter">Ze
      <input type="checkbox" name="checkbox3" value="3" checked/>
      </label>
      </li>
      <li class="list">
      <img src="images/free.jpg" height="100" width="120" />
      <label class="filter">Free
      <input type="checkbox" name="checkbox4" value="6" checked/>
      </label>
      </li>
      <li class="list">
      <img src="images/ad.jpg" height="100" width="120" />
      <label class="filter">Ad
      <input type="checkbox" name="checkbox5" value="5" checked/>
      </label>
      </li>
      <li class="list">
      <img src="images/wol.jpg" height="100" width="120" />
      <label class="filter">Wol
      <input type="checkbox" name="checkbox6" value="1" checked/>
      </label>
      </li>
      </ul>
      <br />
      <!--styled button with class -->
      <input type="submit" name="Submit" value="generate enquiry" style="margin-left:30px;margin-bottom:30px;
background: rgb(255,255,255); /* Old browsers */
background: -moz-linear-gradient(top, rgba(255,255,255,1) 1%, rgba(252,234,187,1) 27%, rgba(252,205,77,1) 45%, rgba(247,147,30,1) 98%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(1%,rgba(255,255,255,1)), color-stop(27%,rgba(252,234,187,1)), color-stop(45%,rgba(252,205,77,1)), color-stop(98%,rgba(247,147,30,1))); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, rgba(255,255,255,1) 1%,rgba(252,234,187,1) 27%,rgba(252,205,77,1) 45%,rgba(247,147,30,1) 98%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, rgba(255,255,255,1) 1%,rgba(252,234,187,1) 27%,rgba(252,205,77,1) 45%,rgba(247,147,30,1) 98%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, rgba(255,255,255,1) 1%,rgba(252,234,187,1) 27%,rgba(252,205,77,1) 45%,rgba(247,147,30,1) 98%); /* IE10+ */
background: linear-gradient(to bottom, rgba(255,255,255,1) 1%,rgba(252,234,187,1) 27%,rgba(252,205,77,1) 45%,rgba(247,147,30,1) 98%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#ffffff', endColorstr='#f7931e',GradientType=0 ); /* IE6-9 */
color:#555555;
-webkit-border-radius: 8px;
border-radius: 8px;
border:1px solid #F7931E;
padding:15px 30px 15px 30px;
font-size:14px;
font-weight:bold;
cursor:pointer;" />
</form>


</div>
</body>
</html>
	<?php
}

Open in new window

Robert Schutt

WHERE gen_enq_cat IN (1, 2, 3, 4, 5, 6)

Open in new window

This doesn't change anything I'm afraid. What I meant was to only include the categories that are chosen by the user in the query.

TLN_CANADA

ASKER

Sorry Robert, I understand what you mean now. I did this, for example selected them with a cat id of 1 (WHERE gen_enq_cat IN (1) ) and it works perfectly this way.

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Schutt

You shouldn't need the while loop when you do it like that. You get 1 record that belongs to 1 of the chosen categories. If not, they don't exist and you can give your message about using a different filter.

TLN_CANADA

ASKER

This gives the error:

SELECT * FROM gen_enquiry_table WHERE gen_enq_cat IN (2,4,3,6,5,1) ORDER BY RAND() LIMIT 1
Warning: mysql_num_rows() expects parameter 1 to be resource, null given in /home/clear555/public_html/module/enquiry/template/default/controller/enq/chenq.php on line 62

Open in new window

Robert Schutt

Since your last post contained echo $query and my post contains $sql, I suspect there's been a mixup somewhere with the change Ray proposed. Your previous code seemed fine (apart from that echo), did you change anything?

Robert Schutt

By the way I just read in the manual that the arguments for implode() can be in either order but should really be the other way around (delimiter first).

TLN_CANADA

ASKER

No, I commented out the other query and just have

$sql = "SELECT * FROM gen_enquiry_table WHERE gen_enq_cat IN (".implode($filters, ',').") ORDER BY RAND() LIMIT 1";
		echo $sql;

Open in new window

TLN_CANADA

ASKER

I didn't take out the while loop though, should I have done that?

Robert Schutt

Ok, because the error seems to indicate you didn't execute the query. Please check that this is still in there:

$query = mysql_query($sql) or...

Open in new window

Robert Schutt

It shouldn't matter but I did take the while loop out. Actually if the goal is just retrieving 1 record then everything to do with $counter and $condition can go. When the chosen categories are in the sql then you either get a correct record or you get nothing and there is nothing and no need to try again.

TLN_CANADA

ASKER

Sorry, I had commented this out too. Yes, it's working fine now, thank you very much!

What was wrong with it before that it was not displaying particular catagory records?

Robert Schutt

Great!

It must have been a coincidence, or the random is not all that random? In any case, returning a random record 6 times does not guarantee that a certain category will be found.

Now that you've got a matching record, checking with in_array() is not necessary anymore either so the code can be compressed quite a bit (like I said, if I understand correctly everything to do with $counter and $condition can be deleted). The only thing that you can't determine with this code is the overall 'table is empty' message but I would hope that is a situation that isn't likely.

TLN_CANADA

ASKER

Perfect, that's right Robert, that situation won't occur. Thank you so much and have a great evening!

Ray Paseur

A great investment of both your time and your money if you think you may be doing a little more with PHP and data bases:
http://www.sitepoint.com/books/phpmysql5/

Best of luck with your project, ~Ray

TLN_CANADA

ASKER

Thank you Ray, I'll check it out.