Solved

Random selection from DB not working correctly

Posted on 2013-02-02
20
228 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 108

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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 108

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now