Major Query - Multiple Tables - RS Paging

Problems selecting just one record...

[Gadnium]

OK...I cannot figure this one out...I think the solution is php and not mySql so Here goes:

I have a sales leads DB that is accessed by my employees whom all have different levels of access (1-9).

The sales leads all come from one DB and the employee access level comes from another.

Every sales lead in the sales DB has been assigned to an employee. Ex:
leadID=3, which_lo=4 as long as which_lo #4 has access level greater than 5.

lead_list_builder.php =
Creates a list of all leads that have been assigned to a particular employee that will link to a details page for that leadID Ex: SELECT leadID, which_lo FROM leads WHERE which_lo = 4;

That part is easy....My problem is on the details page.

lead_details.php=
I can very easily display the details for that lead by: SELECT name, address, etc... FROM leads WHERE leadID=3;

I want the details page to have a full recordset of all 57 leads assigned to that employee just like on the list builder page, except I want to be able to call a specific record(leadID=3) from within my 57 lead recordset: something like:
SELECT leadID, name, address etc...FROM leads WHERE which_lo=4;
This will return all 57 leads though....How do I get to leadID 3 from a recordset of 57?

The Macromedia recordset paging system for php is extremly close to what I want. I just want the added functionality of selecting a specific record from within the paging recordset.

Thanx in advance to whoever can tackle this one.

Gadnium

[Timotheos]

Hi Gadnium and a big welcome to WebmasterWorld!

I think what you're asking for is something like this?

SELECT leadID, name, address etc...FROM leads WHERE which_lo=4 AND leadID=3;

Sorry if I'm misunderstanding but you can make the 'where' part of the statement more complex using functions and operators [dev.mysql.com].

Tim

[Gadnium]

Hi,

SELECT leadID, name, address etc...FROM leads WHERE which_lo=4 AND leadID=3;

Will only place one record in the recordset. With only one record, I will not have paging...

I need all 57 record in the resordset..

Thanks for trying...

I will look at the functions from the link you sent...

[jatar_k]

Welcome to WebmasterWorld Gadnium,

Your question is a little confusing so let me see if I get it.

Your list page works fine and you have paging but you also want the details page to have the list as well as the detail from a specific record?

If so you have 2 choices.

1. you use the query that grabs the whole recordset. As you are going through the results you can assign the values from the row you need detail from to an extra set of variables for displaying the detail.

If you read your results into an array you should be able to access them from that array as well.

2. Do 2 queries on the same page. 1 for the full recordset and one for the single row.

I am not totally sure if I understood correctly though.

[Gadnium]

I think an array is my answer...

I am kinda new so I appreciate the help.

You replied:
1. you use the query that grabs the whole recordset. As you are going through the results you can assign the values from the row you need detail from to an extra set of variables for displaying the detail.

If you read your results into an array you should be able to access them from that array as well.

This is exactly what I want to do... I just don't know how!

I am using Dreamweaver and the recordset is like this:

mysql_select_db($database_conn, $conn);
$query_rsLevel = "SELECT leads.name, etc... FROM leads WHERE which_lo=".$clientID;
$rsLevel = mysql_query($query_rsLevel, $conn) or die(mysql_error());
$row_rsLevel = mysql_fetch_assoc($rsLevel);
$totalRows_rsLevel = mysql_num_rows($rsLevel);

I have been accessing all of my fields like:
$row_rsLevel['name'];

How can I move to row number 3 in this array?

I know using DW is hindering...But I am learning!

I used to write ASP for years... I just started php and mySql 6 months ago and I have made more progress(money too) in the last 30 days than I did in 1 yr with ASP!

Thanx A Lot

[jatar_k]

A thread that may help is this one
Basics of extracting data from MySQL using PHP [webmasterworld.com]

The easiest possible thing to do would be as you are reading results from your query have an if statement that will capture the right row and assign those values to a different set of vars.

from the code you posted I assume there is a loop somewhere for displaying the records. Let's see if we can read the results into an array.

So we came to this detail page from a list so there must be a variable floating around that contains the leadID. I will make a guess that it is coming in through the url in the $_GET array. We need to grab it so we can use it in our loop.

$lid = $_GET['leadID'];

now we have that leadID for later. I won't repeat the connect or db select lines as they aren't relevant.

$query_rsLevel = "SELECT * FROM leads WHERE which_lo=".$clientID;
$rsLevel = mysql_query($query_rsLevel) or die(mysql_error());

perfect, now we have executed our query and either have a result resource to access our data or we have nothing. ;) We won't worry about testing for returns. I saw you needed the number of returned rows so we can do that here.

$totalRows_rsLevel = mysql_num_rows($rsLevel);

we also need to reset $rsLevel after this step to have access to our whole record set again.

mysql_data_seek($rsLevel,0);

now our pointer has been returned to the beginning of the result set. We can now read it into an array and find our specific row. I don't know all of the columns in your query so I can't build this exactly right but you can see the idea.

$counter = 0; 
while ($row_rsLevel = mysql_fetch_assoc($rsLevel)) { 
 if ($row_rsLevel['leadID'] == $lid) $mydetailpos = $counter; 
 $resarr[$counter] = array($row_rsLevel['leadID'],$row_rsLevel['name'],$row_rsLevel['address']); 
 $counter++; 
}

That will leave you with

$lid : the leadID of your detail row
$resarr : an array of the reults for your list
$mydetailpos : the array element at which your detail row is located

that should get you all you need. You could echo the name from your detail row like so

echo $resarr[$mydetailpos];

that help?

[Gadnium]

I think that will do what I am looking for.

By the way, all of your assumptions were correct about linking to the details page with the leadID..

I will give this a shot..

Thanx...

[Gadnium]

I am close to getting this to work...

When I echo $resarr[$mydetailpos];

I get output of:

Array

How can I display the name, address etc.. from
within the $resarr[$mydetailpos]?

Thanx

[Gadnium]

I looked up
array_values() and
array_keys()

This will work...

Is this the most efficient way?

Thanx.

[jatar_k]

sry my fault

it should be

echo $resarr[$mydetailpos]['leadID'];
echo $resarr[$mydetailpos]['name'];
echo $resarr[$mydetailpos]['address'];

since it is a mutlidimensional array you need the last part too, sry