How Do I Add An "and" Statement?

In my table called table_products, I would like to display the value of the field called desc, when:

 

cat = Heating

 

and

 

sub_cat = fittings

 

 

What I have right now will give me the result with ONE field. I am wondering how to incorporate a second "specific value" into my query. Can anyone help with this?

 

<?php	  // Query the Database $specific_value = 'Heating'; $num_columns = 3;$result = mysql_query("SELECT * FROM table_products WHERE cat = '" . mysql_real_escape_string($specific_value) . "'");$counter = 0;while($row = mysql_fetch_assoc($result)) {	// increment counter	$counter++;	// use modulus operator to determine whether this is the first column in row	if ($counter % $num_columns == 1) {		// is first column of row		print '<tr>';	}	echo '<td><font size ="2">'.$row['desc'].'</font></td>';		// use modulus operator to determine whether this is the last column in row	if ($counter % $num_columns == 0) {		// is last column of row		print '</tr>';	}}// fill empty cells in last row if needed$empty_num = $num_columns - ($counter % $num_columns);if ($empty_num > 0){	for($i = 1; $i <= $empty_num; $i++) {		echo '<td> </td>';	}	echo '</tr>'; // close last row}  ?>

Edited September 7, 2006 by ginginca (see edit history)

We will have to know your database and how your tables are tied together unless you're only pulling from one table. See below if you're pulling from multiple tables.

 

Does table_products have a sub_cat field, or is there an entire different table for sub category that's tied through some kind of foreign key?

 

You can use the "AND" clase to seperate the different where statements you have.

 

Example

SELECT * FROM sometable WHERE cond1='one' AND cond2='two'

 

You can also use the or clause and group your statements parenthetically.

 

If it did have a sub_cat field you would change your query to

 

$specific_value = 'Heating';$specific_sub_cat ='fittings';//Case sensitive, it might be Fittings I didn't know$num_columns = 3;$result = mysql_query("SELECT desc FROM table_products WHERE cat = '" . mysql_real_escape_string($specific_value) . "' AND sub_cat='". mysql_real_escape_string($specific_sub_cat)."'");

Personally I don't know why you are using mysql_real_escape_string(). I honestly never have used it. If you're inputting it statically I don't see the point in putting it in a variable then plugging it into your query. The same goes with the sub_cat variable. If you plan to have the user to choose which table, then that could become an issue. I don't know much about security risks, so I'll just go with what you have.

 

Also I noticed you were pulling * in your query. So everything was being pulled which is more work for your database. In order to reduce the work it has to do you could simply pull the only field you want. I read above that you only want desc, so you could change it to pull only that field.

 

Please reply and tell us the structure of your database if this is correct or whatever. If you're trying to pull from 2 tables then I will provide an example here to show you how I typically do it.

 

There are different types of joins and you should do a little reading on which suits your needs. There's outter, inner, left outer etc etc.

 

I just use the standard join which I think is inner, I'm not 100%. What this join does is it will pull from multiple tables if all conditions are met and there are records in ALL TABLES. If something doesn't exist in a table it returns null, so it's up to you on what join to use.

 

Here's an example of joining multiple tables. We're assuming that we have 2 tables. One is 'users' and the other is 'account'. I know this is simple but it might be needed to understand the example. The user table has a userid field, and the Account table shares that field by having a foreign key (which would be part of a primary key). So you join on those 2 like fields.

Example

$Query="SELECT U.name, A.balance, A.lastpayment   FROM users U	  JOIN account A ON U.userid=A.userid   WHERE U.age > 23";

You can tell I used U for users and A for account. It just makes everything go a little smoother if you're trying to pull an item that both tables share. For example, if you tried to pull userid, without using the table Identifiers you would get an error because it wouldn't know where to pull it from. This might not help much so I'll just stop here, but if you have any questions I would be glad to "try" and help you. I'm new to php myself, but I've been programming a lot in it lately.
Edited September 7, 2006 by minnieadkins (see edit history)

Although there are two tables in my database, they are not linked.The data I am pulling for this query is all from the same table.The table is called table_products, and has the following fields:numcatsub_catdescotherWith the exception of num, all fields will be displayed in the query.Thank you for your help!Gin

Thank you for your help!

I have been helped too

Although there are two tables in my database, they are not linked.
The data I am pulling for this query is all from the same table.

The table is called table_products, and has the following fields:

num
cat
sub_cat
desc
other

With the exception of num, all fields will be displayed in the query.

Thank you for your help!

Gin

You're welcome. So I assume you have figured out the problem and understand the solution? The simple

AND sub_cat='fittings'

appended to your sql would do the trick.

Edited September 8, 2006 by minnieadkins (see edit history)

Simply add AND to your qUery string like this:

$result = mysql_query("SELECT * FROM table_products WHERE (cat='" . mysql_real_escape_string($specific_value) . "' AND sub_cat='fittings' ");

Now, if you want a more general functionality, for example, when the values you want to qUery comes from a submited form you can do this:

<?php$specific_value1=mysql_real_escape_string($_POST['cat']);$specific_value2=mysql_real_escape_string($_POST['sub_cat']);$result = mysql_query("SELECT * FROM table_products WHERE (cat='$specific_value1' AND sub_cat='$specific_value2')")  or die(mysql_error());while($row = mysql_fetch_assoc($result)) {	echo $row['desc'] . ' ' . $row[other] . '<br />';}?>

You must use the mysql_real_escape_string function to avoid sql injection attacks, to escape the following characters: \x00, \n, \r, \, ', " and \x1a and to guarantee that the data is safe before you send your query.

Best regards,

I know this is a little off topic, but since it is sorta in this topic, what's the difference in addslashes() and mysql_real_escape_string()

Simply add AND to your qUery string like this:

 

$result = mysql_query("SELECT * FROM table_products WHERE (cat='" . mysql_real_escape_string($specific_value) . "' AND sub_cat='fittings' ");

Now, if you want a more general functionality, for example, when the values you want to qUery comes from a submited form you can do this:

 

[/quote]I'm running into an error with my WHILE statement.  Here's the error:Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/pssi-ftp/public_html/heating_products.php on line 180Line 180 is:  while($row = mysql_fetch_assoc($result)) {Here's the entire segment of code:[code]<?php	  // Query the Database $specific_value = 'Heating'; $num_columns = 3;$result = mysql_query("SELECT * FROM table_products WHERE (cat='" . mysql_real_escape_string($specific_value) . "' AND sub_cat='fittings' ");$counter = 0;while($row = mysql_fetch_assoc($result)) {	// increment counter	$counter++;	// use modulus operator to determine whether this is the first column in row	if ($counter % $num_columns == 1) {		// is first column of row		print '<tr>';	}	echo '<td><font size ="2">'.$row['desc'].'</font></td>';		// use modulus operator to determine whether this is the last column in row	if ($counter % $num_columns == 0) {		// is last column of row		print '</tr>';	}}// fill empty cells in last row if needed$empty_num = $num_columns - ($counter % $num_columns);if ($empty_num > 0){	for($i = 1; $i <= $empty_num; $i++) {		echo '<td> </td>';	}	echo '</tr>'; // close last row}  ?> 

I tried to quote your topic, but I keep getting an unknown page. It seems to happen to me a lot when using the quote options on Xisto.

Anyways the best thing to do IMO opinion is to test your sql before you assume it's right. Basically the error is saying that $result, which is suppose to be holding the query handle is not right.

Looking at your code, I do not see any place where you are connecting to a database. Perhaps you are including it, or it is above. If you don't have the statements to connect to the database then that could be your problem.

$conn = mysql_connect(localhost, $User, $Pass);$dbh = mysql_select_db($Table, $conn);

Replace those variables such as $User, $Pass, and $Table with the appropriate fields. If you do have this in your php file, test your query by outputting it to your screen after it's been parsed by the php, then copy and pasting it into an sql command line.

Example:
change the first few lines of code to this

<?php	  // Query the Database$specific_value = 'Heating';$num_columns = 3;$QString = "SELECT * FROM table_products WHERE (cat='" . mysql_real_escape_string($specific_value) . "' AND sub_cat='fittings' ";echo "<PRE>$QString</PRE>";//debug statement, delete when done$result = mysql_query($QString) OR DIE('There is an error');$counter = 0;

That way you can see your sql before it's executed, and see if you have any mistakes in it.
Notice that I also added a die clause to the mysql_query function, so you won't reach the loop if the query does not succeed.

It's always best to see if you have any results too before you try to loop through them. If you don't have any results you can just bypass that section of code, but that's another story. Hope this helps or at least gets you started in the right direction.
Edited September 11, 2006 by minnieadkins (see edit history)

I know this is a little off topic, but since it is sorta in this topic, what's the difference in addslashes() and mysql_real_escape_string()

the addslashes() function simply escapes with a backslash the single quote ('), double quote ("), backslash (\) and NUL (the NULL byte) and runs automatically on all GET, POST, and COOKIE data when the PHP directive magic_quotes_gpc is on -by default is on- not only with queries.

By the other hand, mysql_real_escape_strings escapes the following characters: \x00, \n, \r, \, ', " and \x1a, another difference is that this function takes into account the current character set of the connection if it is supported by the database. As i read a time ago this function must be use if binary data is to be inserted to the database.

Best regards,