Solved

Random selection from DB not working correctly

Posted on 2013-02-02
20
231 Views
Last Modified: 2013-02-03
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

0
Comment
Question by:TLN_CANADA
  • 9
  • 9
  • 2
20 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38847949
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)
0
 

Author Comment

by:TLN_CANADA
ID: 38847986
Thanks Robert, I will check this out and get back to you.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38848626
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.
http://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

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:TLN_CANADA
ID: 38849279
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

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849314
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38849322
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.
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 38849327
Like this:
		$sql = "SELECT * FROM gen_enquiry_table WHERE gen_enq_cat IN (".implode($filters, ',').") ORDER BY RAND() LIMIT 1";
		echo $sql;

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849329
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38849333
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

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849346
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?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849350
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).
0
 

Author Comment

by:TLN_CANADA
ID: 38849405
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

0
 

Author Comment

by:TLN_CANADA
ID: 38849414
I didn't take out the while loop though, should I have done that?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849417
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

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849433
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38849437
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?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38849455
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38849465
Perfect, that's right Robert, that situation won't occur. Thank you so much and have a great evening!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38849588
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
0
 

Author Comment

by:TLN_CANADA
ID: 38849594
Thank you Ray, I'll check it out.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question