Please Help with WHERE clause

August 7, 201313 yr

Hi

Im making my 1st big website and im having problems with the WHERE clause. im trying 2 get the items 2 list on a page where position = 1 and sub_position = 2 for eg. and just cant get it 2 work

code in functions is

function get_item_for_sub($sel_sub) {

$query = "SELECT * ";

$query .= " FROM stock ";

$query .= " WHERE position = {$sel_catergorie['Id']} ";

$query .= " AND sub_pos = {$sel_sub['Id']}";

$item_set = mysql_query($query);

confirm_query($item_set);

return $item_set;

}

and code on the page is

<?php $sel_catergorie = get_catergorie_by_id($_GET['cat']) ; ?>

<?php $sel_sub = get_sub_cat_by_id($_GET['sub']) ; ?>

<?php $item_set = get_item_for_sub($sel_sub['Id']); ?>

</div>

<?php

while ($items = mysql_fetch_array($item_set)) {

?>

<li>

<?php

echo "<a href=\"?page=product&itm=" . urlencode($items["Id"]) . "\">{$items["item_name"]} </a>";

?>

</div>

Paypal code gose here

</div>

</div>

</li>

</ul>

<?php } ?>

</div>

PS iv taken some code out that dose not need 2 be seen for this topic

Thanks all

August 7, 201313 yr

If you're going to be building a big website, you want to be testing your queries before you implement them into PHP code.

Try testing your query first with MySQL Workbench. It allows you to connect to a DB and test your queries before you put it into code. This is great for getting the exact data you want from your query, reducing load times etc.

Where is your function getting these two array items from?

        $query .=    " WHERE position = {$sel_catergorie['Id']} ";
        $query .=   " AND sub_pos = {$sel_sub['Id']}";

Also, concatenating your variables being used in the query like so:

$query = "SELECT *
          FROM stock
          WHERE position = " . $sel_catergorie['Id'] . "
          AND sub_pos = " . $sel_sub['Id'] . "";

Have you got the spelling correct of your variables etc?

Edited August 7, 201313 yr by Yakindo

August 7, 201313 yr

Author

Thanks Yakindo for the like that will save me loads of time.

The Arrys come from the top of the page in the GET_ function i think lol and spelling should be fine thanks

PS iv been trying 2 put something like this :

function get_item_for_sub($sel_sub, $sel_catergorie) { // putting 2 arrys in here

$query = "SELECT *

FROM stock

WHERE position = " . $sel_catergorie['Id'] . "

AND sub_pos = " . $sel_sub['Id'] . "";

$item_set = mysql_query($query);

confirm_query($item_set);

return $item_set;

}

Edited August 7, 201313 yr by PumpkinHead

August 7, 201313 yr

Thanks Yakindo for the like that will save me loads of time.

The Arrys come from the top of the page in the GET_ function i think lol and spelling should be fine thanks

PS iv been trying 2 put something like this :

function get_item_for_sub($sel_sub, $sel_catergorie) { // putting 2 arrys in here

$query = "SELECT *

FROM stock

WHERE position = " . $sel_catergorie['Id'] . "

AND sub_pos = " . $sel_sub['Id'] . "";

$item_set = mysql_query($query);

confirm_query($item_set);

return $item_set;

}

Right for that function, all you need to do is pass through the category and the sub id, not an array of data. Just two variables (or pieces of data).

<?php

    function get_item_for_sub($sel_sub, $sel_cat) {
        $query = "SELECT *
                  FROM stock
                  WHERE position = " . $sel_cat . " 
                  AND sub_pos = " . $sel_sub . "";
       $item_set = mysql_query($query);
       confirm_query($item_set);
       return $item_set;
    }


    $idkRandomVar = get_item_for_sub($sel_sub['id'], $sel_cat['id']);

?>

Then all you need to do is loop through data which has been returned, I don't know if this is any help, could you provide more code (obviously take out any sensitive information)

August 7, 201313 yr

Author

dont know if this helps but this is the errror im getting

Database query failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'AND sub_pos = 3' at line 4

August 7, 201313 yr

Author

This is all the code i think

results.php

<div id="resultsPage">
	
	<?php $sel_catergorie = get_catergorie_by_id($_GET['cat']) ; ?>
	<?php $sel_sub = get_sub_cat_by_id($_GET['sub']) ; ?>
	<?php $item_set = get_item_for_sub($sel_sub['Id']); ?>

	<div class="resultHome">
		<h2><?php echo $sel_sub['menu_name']; ?></h2></br>
		<p><?php echo $sel_sub['content']; ?></p></br>
	</div>
	<?php 
		while ($items = mysql_fetch_array($item_set)) { 					
	?> 
	<ul class="resultSetBox">
		<li>
			<div class="resultTitle">
				<?php 
					echo "<a href=\"?page=product&itm=" . urlencode($items["Id"]) . "\">{$items["item_name"]} </a>"; 	
				?>
			</div>
			<div class="resultPriceBox">
				<div class="resultPrices">£<?php echo $items['rrp']; ?>.00</div>
				
				
				Paypal code gose here

				
				
			</div>
			<div class="resultDis"><?php echo $items['sm_description']; ?></div>
			
			<div class="resultImageOut">
				
			</div>
			
		</li>
		
	</ul>
<?php } ?>
</div>

and this is the functions.php page

<?php
	// This file is the place to store all basic functions

	function confirm_query($result_set) {
		if (!$result_set) {
			die("Database query failed: " . mysql_error());
		}
	}
	
	function get_all_subjects() {
		$query = "SELECT * 
				FROM subjects 
				ORDER BY position DESC";
		$subject_set = mysql_query($query);
		confirm_query($subject_set);
		return $subject_set;
	}
	
	function get_pages_for_subject($subject_id) {
		$query = "SELECT * 
				FROM pages 
				WHERE subject_id = {$subject_id} 
				ORDER BY position ASC";
		$page_set = mysql_query($query);
		confirm_query($page_set);
		return $page_set;
	}

	function get_all_catergories() {
		$query = "SELECT * ";
		$query .=	"FROM catergories ";
		$query .=	"WHERE visible = 1 ";
		$query .= "ORDER BY position ASC";
		$catergories_set = mysql_query($query);
		confirm_query($catergories_set);
		return $catergories_set;
	}
	
	function get_pages_for_sub_cat($catergorie_id) {
		$query = "SELECT * ";
		$query .="FROM sub_cat "; 
		$query .="		WHERE catergorie_id = {$catergorie_id} "; 
		$query .="		AND visible = 1 ";
		$query .="		ORDER BY position ASC";
		$sub_cat_set = mysql_query($query);
		confirm_query($sub_cat_set);
		return $sub_cat_set;
	}

	function get_catergorie_by_id($catergorie_id) {
		$query = "SELECT * ";
		$query .= "FROM catergories ";
		$query .= "WHERE Id=" . $catergorie_id ." ";
		$query .= "LIMIT 1";
		$result_set = mysql_query($query);
		confirm_query($result_set);
				//REMEMBER:
		// if no rows are returned, fetch_array will return false
		if ($catergories = mysql_fetch_array($result_set)) {
			return $catergories;
		} else {
			return NULL;
		}
	}
	
	function get_sub_cat_by_id($sub_cat_id) {
		$query = "SELECT * ";
		$query .= "FROM sub_cat ";
		$query .= "WHERE Id=" . $sub_cat_id ." ";
		$query .= "LIMIT 1";
		$result_set = mysql_query($query);
		confirm_query($result_set);
		//REMEMBER:
		// if no rows are returned, fetch_array will return false
		if ($sub_cat = mysql_fetch_array($result_set)) {
			return $sub_cat;
		} else {
			return NULL;
		}
	}
	
	function find_selected_page() {
		global $sel_catergorie;
		global $sel_sub;
		if (isset($_GET['cat'])) {
			$sel_catergorie = get_catergorie_by_id($_GET['cat']);
			$sel_sub= NULL;
		} elseif (isset($_GET['sub'])) {
			$sel_catergorie= NULL;
			$sel_sub = get_sub_cat_by_id($_GET['sub']) ;
		} else {
			$sel_catergorie = NULL;
			$sel_sub = NULL;
		}
	}

	function get_item_for_sub($sel_sub) {
		$query = "SELECT *
				FROM stock
				WHERE position = " . $sel_catergorie['Id'] . "
				AND sub_pos = " . $sel_sub['Id'] . "";
		$item_set = mysql_query($query);
		confirm_query($item_set);
		return $item_set;
	}
		function get_products($sel_item) {
		$query = "SELECT * ";
		$query .=	"FROM stock ";
		$query .=	"WHERE Id= $sel_item"; 
		$product_set = mysql_query($query);
		confirm_query($product_set);
		return $product_set;
	}

?>

Please Help with WHERE clause

Featured Replies

Create an account or sign in to comment

Recently Browsing 0

Member Statistics

Forum Statistics

Join WDF

Account

Navigation

Search

Configure browser push notifications

Chrome (Android)

Chrome (Desktop)

Safari (iOS 16.4+)

Safari (macOS)

Edge (Android)

Edge (Desktop)

Firefox (Android)

Firefox (Desktop)