What's wrong with this query?

General PHP Help
#1

When the code below is called, the admin has just logged in and is only supposed to see the products linked to his/her customer id number, or custid.

The admins table contains admins who have a unique adminid and a customer id, or custid. This custid number is associated with a unique customer (my clients) in the customers table. I’m using the customers table to ‘associate’ the admins to the customers.

I’m trying to make the php get the custid number by saying SELECT custid from customers (my clients table) where adminid=adminid (the admin id of the person who just logged in).

This code is pulling ALL the products from the products table, regardless of which admin is logged in. A serious problem. Any help is appreciated. Thanks.

[php]<?php

 echo "<label><h3>Browse Procucts:<br></h3> </label><br>\n";
     $query="SELECT custid from customers where adminid=adminid";
     $result=mysql_query($query);
     while($row=mysql_fetch_array($result,MYSQL_ASSOC))
     {
     $custid = $row['custid'];
         
         $query2="SELECT count(prodid), productname FROM products WHERE custid = $custid";
         $result2 = mysql_query($query2);
         $row=mysql_fetch_array($result2);
         $total = $row[0];
         $entreename = $row['productname'];
         echo "<a href=\"smalladmin.php?content=testpage&custid=$custid\">$productname</a> ($total)<br>\n";
     }

?>[/php]

#2
hello traydavid,

i think your $query is wrong.
$adminid is define some where or you have to get admin id from database.
And your $query should be like this
$query=“SELECT custid from customers where adminid=”.$adminid;

AND

your $query2 is like this
$query2=“SELECT count(prodid), productname FROM products WHERE custid =”.$custid;

And Replace below code
echo “<a href=“smalladmin.php?content=testpage&custid=$custid”>$productname ($total)
\n”;
with my code
echo “<a href=“smalladmin.php?content=testpage&custid=”.$custid.”">".$productname." (".$total.")
\n";

i hope this will helpful for you and direct you in correct direction.
Reply your feedback
SR

#3

Yes, really. So happens. Let’s discuss this question. Here or in PM.

#4

Also, one thing TrayDavid, is you use $row in the first query and again in the second inside a While using the $row… Can’t do that!

You use While ($row=… and INSIDE that while, you change $row… So, the while will get messed up…
You change $query to $query2, but, not $row… Hope that helps, too…

#5
hello Autobrofaw, sure we can discuss this question here as well as PM.
#6

Thank you all very much. I’ll start looking into these options and let you know. By the way, why the periods in front of the words, the .$adminid format? That’s new for me. Thanks.

#7

ErnieAlex,

What should I have changed $row to? $row2? Thanks.

#8

Well, first, a period in a string assignment means concatenation. Similar to + or & in other types of programming… So $xyz=“hello” . " " . “TrayDavid”; will give you “hello TrayDavid” … (note the space)
So, in creating a query, you would use the query’s text and add in the variables from other sources…
Such as “select * from table where id=” . $UserID… This would combine the SELECT cause and the data inside the $UserID variable which you can pass to the database… Hope that makes sense…
Anyway, here is your code you posted with minor changes. Try it and let us know if it fixes it…
[php]

<?php echo "

Browse Products:


\n"; $query="SELECT custid from customers where adminid=adminid"; $result=mysql_query($query); while($row=mysql_fetch_array($result,MYSQL_ASSOC)) { $custid = $row['custid']; $query2="SELECT count(prodid), productname FROM products WHERE custid = $custid"; $result2 = mysql_query($query2); $row2=mysql_fetch_array($result2); $total = $row2[0]; $entreename = $row2['productname']; echo "" . $productname . " (" . $total . ")
\n"; } ?>

[/php]
I am not sure this will work, but, I think it should. Let us know. As far as the extra " . " (periods) that I put in, it is to make the lines easier to read and makes the variables easier to see where they fit into the line. Good luck…

#9

Thank you. I tried it and got this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\store\admin\smalladminmain.inc.php on line 32

Line 32 is this line

[php]<?php
echo “

Browse Products:


\n”;
$query=“SELECT custid from customers where adminid=adminid”;
$result=mysql_query($query);
while($row=mysql_fetch_array($result,MYSQL_ASSOC)) {
$custid = $row[‘custid’]; 
         $query2="SELECT count(prodid), productname FROM products WHERE custid = $custid";
         $result2 = mysql_query($query2);

//—the line below this is line 32-------------

         $row2=mysql_fetch_array($result2);
         $total = $row2[0];
         $entreename = $row2['entreename'];
         echo "<a href=\"smalladmin.php?content=tester&custid=" . $custid . "\">" . $entreename . "</a> (" . $total . ")<br>\n";
         }

?>[/php]

#10

Well, that error says the query is bad. But, I did notice this error too… Let’s fix this one and let me know…

This line:
$query=“SELECT custid from customers where adminid=adminid”;
(in the first query) assigns adminid to itself… I think you want it to be adminid=$adminid … Correct?

Also, in the second query are there multiple results for that link? I mean, is there one entry returned? If there are more than one, we have to have a second WHILE similar to the first query. It’s very hard to debug other’s code without knowing the entire picture… Let me know…

#11

The count function wouldn’t throw that error would it? Thanks.

#12

Thank you. I can appreciate that.

The second query is calling up products from the products table that are associated with the customer id number of the admin. Each admin has an admin id number (to identify each unique administrator) and a customer id number associated with said admin. I’m trying to make the code select all the products in the products table that have the same customer id number as the admin has. The only way to find out the admin’s customer id number is to first query the admins table where this data is located.

The admin table has admin name, admin id number, admin customer number, admin password.

Query the admins table when admin logs in to get the admin’s customer id number, or the company he works for, called a customer in my database. Compare that customer id number to all the products in the products table. This would tell us which products belong to this admin’s customer, or employer. Then display those on the page for the admin to see/change.

Hope this helps. Yes, there are going to be many products, about 20-30, that match each admin’s customer id number.

#13

I changed adminid to $adminid. Now it says that variable is undefined.

That’s the problem I’m having. I’m trying query the ADMINS table and get the admin’s id number who just logged in. Then pull that admin’s customer id number from the ADMINS table.

THEN compare this number (customer id number) to the products in the PRODUCTS table against each product’s customer id number. This would then display all the products that belong to that customer, based on which admin has logged in. I call it a double query because I’m trying to query one table and get that data, temporarily hold it and use it to pull data out of a second table using a second query.

I know this is difficult problem. Two weeks for me now. Thanks for all the thought.

#14

more info…

admins table: name, password, adminid number, customer id number

products table: product name, customer id number, price, description, etc.

Query one: an administrator just logged in. Query the admins table and get his customer id number.

Query two: compare that customer id number to all the products in the products table and display all the products that have the same customer id number as this admin

Hope this helps.

#15

I really think it is a simple problem, it’s just trying to solve it long distance… LOL
And, I have been busy… Anyway, got time now and the next couple days… Let’s solve it…

Okay, where is the admin’s id coming from? Is this code you posted, did the admin number come from a form? You are basing your first query on it and I assumed the problem was in the second query, not the first. I am sorry I didn’t catch it at first. Normally, when you let a user or admin log in, you check their userid and password. When they are validated and okay’d, you usually save the userid or adminid in a variable or session variable. Then, you use that for your query. Like your first query. Where do you get your adminid from?

#16

I hear you. And thanks. I guess that’s the problem. Where do I get the admin’s info and how to hold it for the query? Never thought of that.

Here is the html login form:

[code]


Store Web Site Administration

Please log in below

User Name:



Password:



[/code]

And here is the validate php code:

[php]<?php
session_start();
include ("…/mylibrary/smalllogin.php");
login();

$userid = $_POST[‘userid’];
$password = $_POST[‘password’];

$query = “SELECT userid, name from admins where userid = ‘$userid’ and password = (’$password’)”;
$result = mysql_query($query);

if (mysql_num_rows($result) == 0)
{
echo “

Sorry, your account was not validated.


\n”;
echo “<a href=“smalladmin.php”>Try again
\n”;
} else
{
$_SESSION[‘store_smalladmin’] = $userid;
header(“Location: smalladmin.php”);
}
?>
[/php]

The other code we have been discussing is on the page that the admin would be referred to when he logs in successfully, called smalladmin.php

Thanks.

#17

Well, you store the userid in a variable: $_SESSION[‘store_smalladmin’] = $userid;
This gives you the session variable that is available on any page that starts with session_start();
So, assuming that this USERID is what your admin id is, you would change your first query from
where adminid=adminid"; to where adminid=" . $_SESSION[‘store_smalladmin’];

(Remember to make sure your first line of PHP code is session_start(); )

Try that and let me know… It’s midnight here and I am crashing soon… Not sure how much longer I will be awake… Good luck…

#18

Thank you. Have a good one.

#19

You too, make sure you let me know if that works… Then we can sort out the rest tomorrow… Nite!

#20

Busy day. Late posting…

Seems to ‘want to work’ but I’m getting that same error that the code was expecting a boolean response. I can’t see anything that would cause that. Thanks.

Sponsor our Newsletter | Privacy Policy | Terms of Service