Query question

General PHP Help
#1

This is a query, maybe a ‘double query’ that I am using. The customer id is an id number for the customer. The customer name is the associated name. The prodid is a product id number for a product(s) that the customers sell in the php storefront.

The issue is this: I want to only show the products that are associated with a certain customer in the table. In other words, if an administrator from store 1 logs in, I only want that admin to see products associated with the customer he/she represents. However, this query seems to be showing all products in the database without regard for the customer id. Yes, each product has a customer id associated with it. Each admin has a customer id associated with it.

I’d like php/mysql to query one table (admins) to see which customer id the admin is associated with AND display only those records from the products table with that admin’s customer id number.

Any ideas? Thanks in advance.

[php]
$query=“SELECT custid, custname from customers where adminid=adminid”;
$result=mysql_query($query);
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
{

     $custid = $row['custid'];
         $restname = $row['custname'];
         $query2="SELECT count(prodid) FROM products WHERE custid = $custid";
         $result2 = mysql_query($query2);
         $row=mysql_fetch_array($result2);
         $total = $row[0];
         echo "<a href=\"smalladmin.php?content=testerpage&custid=$custid\">$custname</a> ($total)<br>\n";
     }

[/php]

#2

Normally, you would do it more this way:
[php]

<?PHP $query="SELECT custid, custname from customers where adminid=adminid"; $result=mysql_query($query); // handle whatever that has to do with custname and cusid like are they allowed here... // if they are not allowed here, move them (redirect) to a "You are not allowed here!" page... //At this point, they are allowed, now deal with other stuff... $row=mysql_fetch_array($result,MYSQL_ASSOC)) $custid = $row['custid']; $restname = $row['custname']; $query="SELECT * FROM products WHERE custid = $custid"; $result = mysql_query($query); // Loop thru products... while($row=mysql_fetch_array($result)) echo "$restname ($total)
\n"; } ?>

[/php]
Well, in your example, you only need one query at a time. One to check if it is a valid user logging in and one for the selection of their products… So, no need for multiple copies of the query’s or nesting them… Not really sure what the ($total) was all about? Your code seems to display some sort of link to that calls another page based on custid and restname, but, does not use the data inside the product query. That does not make sense… But, maybe to you… Hope that helped…

#3

At the point of this code, they have already logged in and been redirected to this page. This query is trying to:

  1. Determine the customer ID associated with the admin ID (one query)
  2. Get the products from the products table with the same customer ID as the admin’s customer ID
  3. Display these products for the admin to edit
  4. Display is achieved by calling a ‘tester’ page that loops through the product data once it is selected as described in 1-3 above and displays it.

Thanks.

#4

sorry, I missed some of my open replies… Did you get this one fixed? If not repost some code and I will look into it for you…

#5

It’s similar enough to the one we’re working on elsewhere that it’s okay that I didn’t get it fixed. We’re still kind of working on it. Thanks.

#6

You should continue following the original thread.
As stated in that thread
[php]$query=“SELECT custid, custname from customers where adminid=adminid”;[/php]
adminid=adminid will always result with true, so you are retrieving all the customers.
this should have a variable or a JOIN somewhere

Other than that the snippet of code is pratically worthless without your db schema (table fields) and how they are in relation to each other.

#7

Thanks for your help guys. Still working on it but with limited time (work schedule).

I’ve tried adding the .php to showproducts3 like you suggested Ernie and no success. Also reviewed some other pages I have similar that work and they are structured without .php at that location. The code does refer to another page as central content, you’re right.

I’ll continue to work on it and look forward to any ideas you have on this topic. Thanks again.

#8

I’m around the next couple days if you want some further help with this project. Just repost your current code and we can attack it further if you wish!

#9

Thanks, Ernie. Appreciate it.

Sponsor our Newsletter | Privacy Policy | Terms of Service