[SOLVED] How do I call multiple, specific rows from from my database?

[b]

I have tried a couple different forums, and my hope lies with you guys.  I doubt it's too complicated if you know what you're doing - which I do not. 

 

I wrote a simple PHP script for my business that allows for a customer the ability to access his/her account information (a watch repair business) by typing in a ticket number.  The script called up all the fields from the database, informing the customer what the status of his/her repair job.

 

But now I'm stuck on trying to write a script that will allow various managers from various stores the ability to use a given store identification number, enter into a form, and calling up ALL the stores from the database with that unique identification number.  For example (maybe I'm being redundant, but bear with me...), if I have 20 store owners, and in my database I have stored the total repair jobs from all the 20 stores (a couple thousand), each labeled (field) with a unique store identification number - what script (or function) would I need to allow each store owner the ability to type in #7200 into a form and pull all the stores with #7200 in my database, displaying all the fields relevant to each of their customers.  Is that possible? 

 

An example database (in phpMyAdmin) I have a database (parsec) with a table (retailers) that has 5 fields (id, name, email, repair_number, in_date, est_date) and 4 rows of customer information (which in reality would be a thousand or so).  2 customers has an 'id' of 7000, and 2 customers has an 'id' of 8000.  My goal for the store owner 1 the ability to call up all the fields for 'id' 7000, and store owner 2 the ability to call up all the fields for 'id' 8000.

 

The code I have written now just doesn't seem to work, so I will wait to post it.  I will if anyone wants me to. 

 

Any help would be greatly appreciated!   

 

 

 

 

[prcollin]

you can just do

 

$result = mysql_query("SELECT * FROM retailers

WHERE storeid='7200'");

 

while($row = mysql_fetch_array($result))

  {

  echo $row['Row_Title'] . " " . $row['Row_Title2'];

  echo "<br />";

  }

 

?>

 

i think thats all once you are connected to the db.  assuming you have connected already

[b]

 

Thanks for your reply.

 

I have no trouble connecting to the database, but perhaps I didn't specify that this is basically open to the public.  Where a Store owner can access my website and put in his/her specific store number and pull all the fields/data specific to that store number from my database.

 

Could I just put in something like this:

 

$result = mysql_query("SELECT * FROM retailers
WHERE storeid='7200', '8200', '4300', '3456', '6000', 8000, 3333, 93253");

while($row = mysql_fetch_array($result))
  {
  echo $row['Row_Title'] . " " . $row['Row_Title2'];
  echo "
";
  }

?>

 

Thanks again!

[.josh]

Table looks like this:

 

     

idnameemailrepair_numberin_dateest_date

 

     

7000Johnemail1234somedatesomedate

 

     

7200Joeemail5678somedatesomedate

 

     

8000Janeemail9012somedatesomedate

 

     

7000Jimmyemail4412somedatesomedate

 

     

8000Julyemail9012somedatesomedate

 

 

Code looks like this:

$query = "select * from table where id = 7000";
$result = mysql_query($query);

while ($list = mysql_fetch_assoc($result)) {
   foreach ($list as $key => $val) {
      echo "$key : $val <br />";
   } // end foreach
   echo "<br />";
} // end while

 

Output looks like this:

id : 7000

name : John

email : email

repair_number : 1234

in_date : somedate

est_date : somedate

 

id : 7000

name : Jimmy

email : email

repair_number : 4412

in_date : somedate

est_date : somedate

[b]

wow, that seems to be the best progress made yet...I must be doing something wrong still, but I think it's close:

 

<?php

//connection

$con = mysql_connect("localhost","root","...");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("...", $con);

//query

$query = "SELECT * FROM retailers WHERE id = 7000";
$result = mysql_query($query);

while ($list = mysql_fetch_assoc($result)) {
   foreach ($list as $key => $val) {
      echo "$key : $val <br />";
   } // end foreach
   echo "<br />";
} // end while

//store id

echo "<th>Store ID</th>";

echo "<td>" . $list['id'] . "</td>";
echo "</tr>";

//name

echo "<th>Customer Name</th>";

echo "<td>" . $list['name'] ."</td>";
echo "</tr>";

//email

echo "<th>Email</th>";

echo "<td>".$list['email']."</td>";
echo "</tr>";

//repair number

echo "<th>Repair Number</th>";

echo "<td>".$list['repair_number']."</td>";
echo "</tr>";

//in date

echo "<th>In Date</th>";

echo "<td>".$list['in_date']."</td>";
echo "</tr>";


//est date

echo "<th>Estimated Date</th>";

echo "<td>".$list['est_date']."</td>";
echo "</tr>";



echo "</table>";
?>

 

 

The result I'm receiving is this: 

 

Store IDCustomer NameEmailRepair NumberIn DateEstimated Date

 

 

 

What do you suggest?

 

Thanks very much!

[roopurt18]

What's this output?

 

<?php

//connection

$con = mysql_connect("localhost","root","...");
if( !$con ) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("...", $con);

//query

$query = "SELECT * FROM retailers WHERE id = 7000";
$result = mysql_query($query);

while ($list = mysql_fetch_assoc($result)) {
   echo '<pre style="text-align: left;">' . print_r($list, true) . '</pre>';
} // end while
?>

 

If it outputs personal data, replace it with fake data.  If there's a lot of it, just give the first couple of rows.

[.josh]

each time your while loop iterates, an internal pointer is moved from the current position in the $list array to the next.  By the time the loop ends, $list is set to FALSE, as it has reached the end of the result source (that's why you get all those blanks). 

 

You need to put your echoes inside the while loop. You can make use of the foreach loop to echo your stuff out in the format you want it (the $key is the column name, the $val is the data) or just take out the foreach loop and put your echo stuff in there instead.

 

edit:

 

That of course, assumes that you're even getting info displayed from the foreach loop.  If not, see roopurt's post, and then see mine. 

[b]

Thanks for both of your help.  With this code:

 

<?php

//connection

$con = mysql_connect("localhost","root","...");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("...", $con);

//query

$query = "SELECT * FROM retailers WHERE id = 70724";
$result = mysql_query($query);

while ($list = mysql_fetch_assoc($result)) {
   foreach ($list as $key => $val) {
      
echo "$key : $val <br />";
   
} // end foreach
  
	echo "<br />";


//store id

echo "<th>Store ID</th>";

echo "<td>" . $list['id'] . "</td>";
echo "</tr>";

//name

echo "<th>Customer Name</th>";

echo "<td>" . $list['name'] ."</td>";
echo "</tr>";

//email

echo "<th>Email</th>";

echo "<td>".$list['email']."</td>";
echo "</tr>";

//repair number

echo "<th>Repair Number</th>";

echo "<td>".$list['repair_number']."</td>";
echo "</tr>";

//in date

echo "<th>In Date</th>";

echo "<td>".$list['in_date']."</td>";
echo "</tr>";


//est date

echo "<th>Estimated Date</th>";

echo "<td>".$list['est_date']."</td>";
echo "</tr>";



echo "</table>";

} // end while
?>

 

My output is:

 

customer_id : 1

id : 70724

name : Jeff Bauer

email : jeffbauer@jeffbauer.com

repair_number : 114220

in_date : 2008-05-28

est_date : 2008-05-30

 

Store ID70724Customer NameJeff BauerEmailjeffbauer@jeffbauer.comRepair Number114220In Date2008-05-28Estimated Date2008-05-30 *this is fake information

 

I moved the forwhile loop to cover all the echos, but obviously I'm missing something.  Also, one of the problems (aside from format) is allowing different Store Owners with different id's access to their store information.  This allows me to pull all the records for 70724, but how do I allow the store owner of 87302 to access their customer information?

 

Thanks again.

[.josh]

To answer your first question, the only thing wrong I'm seeing is that you're having issues formating your results.  I'm not really seeing where your opening table tag is, but it should be somewhere before the while loop.  Also, your closing table tag should be at the end, after your loop.  Other than that, I can't really give you a solid answer without knowing what you're wanting the results to look like.

 

To answer your second question, you need to make a form for the user to input the store number and replace the hardcoded number in your query string with the posted value.  But even then, this doesn't really stop userA from looking at userB's info.  For that, you'd need to make a login system.

 

 

[b]

Ok, I'm starting to understand it a little better.  It takes me a little longer than most, I suppose.  Here's the code I have now: 

 

<?php

//connection

$con = mysql_connect("localhost","root","rilke123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("parsec", $con);

//query

$query = "SELECT * FROM retailers WHERE id = 40021";
$result = mysql_query($query);

echo "<tr>";

while ($list = mysql_fetch_assoc($result)) {
   
//customer id

echo "<th>Customer ID : </th>";
echo "<td>" . $list['customer_id'] . "</td>";
echo "<br />";


//store id

echo "<th>Store ID : </th>";
echo "<td>" . $list['id'] . "</td>";
echo "<br />";

//name

echo "<th>Customer Name : </th>";
echo "<td>" . $list['name'] ."</td>";
echo "<br />";

//email

echo "<th>Email : </th>";
echo "<td>".$list['email']."</td>";
echo "<br />";

//repair number


echo "<th>Repair Number : </th>";
echo "<td>".$list['repair_number']."</td>";
echo "<br />";

//in date

echo "<th>In Date : </th>";
echo "<td>".$list['in_date']."</td>";
echo "<br />";

//est date

echo "<th>Estimated Date : </th>";
echo "<td>".$list['est_date']."</td>";
echo "<br />";


} // end while

echo "</tr>";

?>

 

And here is the output:

Customer ID : 3

Store ID : 40021

Customer Name : Cormac McCarthy

Email : mccarthy@theroad.com

Repair Number : 12345

In Date : 2008-05-22

Estimated Date : 2008-05-29

Customer ID : 4

Store ID : 40021

Customer Name : Adam Dunn

Email : dunn@istrikeout.com

Repair Number : 56789

In Date : 2008-05-07

Estimated Date : 2008-05-08

 

That's quite a bit of progress.  I'm not going to worry about format until I can actually retrieve the results, but my next questions (and I must be getting annoying now) are how to make a break between customer id's and also (and most importantly) how do I allow multiple users to put in different store id's to retrieve their information?  I have created a seperate form for the user (as you said), but I'm not sure how 'replace the hardcoded number' or put in multiple hardcoded numbers...Could I just do this:

 

$query = "SELECT * FROM retailers WHERE id = 40021 OR id = 70045, etc.";

 

That doesn't seem to work however. 

 

Hopefully I'm not overly annoying

[.josh]

okay so you have a form

 

basic form:

<form action="process.php" method="post">
   <input type = "text" name = "storeid">
   <input type = "submit" value = "lookup">
</form>

 

now on process.php (the code you have above), instead of a hardcoded number in your query string, you would use the posted value:

 

// this is not a secure way to do this. Let's focus on making it work first. 
$storeid = $_POST['storeid']; 
$query = "SELECT * FROM retailers WHERE id = '$storeid'";

 

oh and to answer breaking a line between customer id's, just add another br tag in the last echo br before your closing while loop }

 

echo "<br /><br />"; 

[b]

 

Thanks very much!  It works great now.  No problems.  I'm almost afraid to ask how to secure it...but for now, the problem is solved.  Thanks again!

[.josh]

The main thing you want to do is prevent sql injection.  There are a ton of tutorials/examples on the internet to show you how to sanitize form input.  It's relatively easy to do, and I suggest you look into it.