Could someone with a bit of Talent help on this one.

[todayme]

I have the code below in the space between //-------------- I need to use the value of $LeadID and do a query on the second table to find out all the values and then assign the highest value to a variable $coolstatus.

 

$LeadID is the primary key in the first table, so that needs to be used to find the relating records in the second table, as this is a one to many relationship.  In these records on the second table there is a field called Status, the values with in this field are one of four.

 

Received

Pending

Sold

Closed

 

Closed and Sold are equal and the highest Pending is the second lowest and Receved is the lowest rating, I need to find out the highest value and then assign it to a variable.

 

Thats it, any takers?  I am guesing it will be a loop inside that loop down there !

 

<?
Print $userid;



echo '<table class="tbl" width="100%" border="0" cellpadding="4" cellspacing="1">
    <tr>
        <td width="80"><b>Lead Id</b></td>
        <td width="110"><b>Industry</b></td>
<td width="110"><b>Location</b></td>
<td width="110"><b>Date</b></td>
<td width="110"><b>Contact</b></td>
<td width="70"><b>Replies</b></td>
        <td><b>Status</b></td>
   
    </tr>';

// Define your colors for the alternating rows

$color1 = "#ffffff"; 
$color2 = "#CCFF99"; 
$row_count = 0;


// Perform an statndard SQL query:
$sql_events = mysql_query("SELECT * FROM _Leads WHERE LINKID = '$userid'")
or die(mysql_error());


// We are going to use the "$row" method for this query. This is just my preference.
while ($row = mysql_fetch_array($sql_events))
{
    $LeadID = $row['ID'];
    $Industry = $row['Industry'];
    $Location = $row['Location'];
    $Date = $row['Date'];
    $Contact = $row['Contact'];

//-------------------------------------------------------












//-------------------------------------------------------






    /* Now we do this small line which is basically going to tell 
    PHP to alternate the colors between the two colors we defined above. */

    $row_color = ($row_count % 2) ? $color1 : $color2;

    // Echo your table row and table data that you want to be looped over and over here.

    echo '<tr>
    <td width="80" bgcolor="'.$row_color.'" nowrap>'.$LeadID.'</td>
    <td bgcolor="'.$row_color.'">
    <a>'.$Industry.'</a></td>


<td bgcolor="'.$row_color.'">
    <a>'.$Location.'</a></td>

<td bgcolor="'.$row_color.'">
    <a>'.$Date.'</a></td>

<td bgcolor="'.$row_color.'">
    <a>'.$Contact.'</a></td>

<td bgcolor="'.$row_color.'">
    <a><form method="POST" action="/replies.php">
    <input type="submit" value="'.$LeadID.'" name="getreplies">
</form></a></td>

<td bgcolor="'.$row_color.'">
    <a>'.$coolstatus.'</a></td>

    </tr>';

    // Add 1 to the row count

    $row_count++;
}

// Close out your table.

echo '</table>';

mysql_close($db);

if ($row_count < 1 )

{


echo '<table border="0" cellpadding="3" cellspacing="3" style="border-collapse: collapse" bgcolor="#CCFFCC" width="100%" id="AutoNumber1">
  <tr>
    <td width="100%"><font size="3">'; Print "Please select the creat lead link, to start generating income."; echo '</font></td>
  </tr>
</table>';
}



?> 

[emehrkay]

this is where im lost

 

values and then assign the highest value to a variable $coolstatus.

[todayme]

Okay well say you return back 4 records, these 4 records could look like this.

 

Received.

Pending

Pending

Sold

 

Sold is the highest value so then $coolstatus = "Sold".  Lets say the records returned were like this.

 

Received

Closed

 

Then the highest value would be Closed and in this case the variable $coolstatus = "Closed"

 

 

Does that explain enough or do you want me to try again?  Thanks heaps for helping.

[emehrkay]

i dont understand how you're saying highest when you're talking about strings.

 

what exactly is returned? is it a single row or an arry of values? are those boolean field names?

 

i think i just need more info to help you out

[artacus]

Ok, well you'll need to first to a subquery to see what the max() status was. Then you'd return any records where the status matched max_status from the subquery.

[todayme]

I understand that they are string values, but cant you do some fancy code that maybe gives each string a weighting value that is numeric?  Or isnt that possible with PHP.

 

Cant you say something like

 

If $String = "Sold"

Status.Value = 5

If $String = "Pending"

Status2.value = 1

 

I am not a programmer in PHP so I am only guesing something like this and a loop and the sql query can do it.???  Does this help you understand anymore ?

 

 

[todayme]

Could someone start the code off so we can start to work through it, if you dont get all of it just put what you have worked out down on the page.  I really am a beginner at the syntax I only understand the logic.

[artacus]

Dont you have the entries in your status table ordered lowest to highest? I was guessing your status table would look something like so

 

id  name
1   Recv'd
2   Pending
3   Sold
4   Closed

 

or whatever would be lowest to highest.

[todayme]

The second table has an incremental key, as well as the field that coresponds to the first table. eg.

 

ID    LINKID    NOTE    STATUS

 

1          2          bla        Received

2          2          bla        Pending

3          3          bla        Received

4          4          bla        Received

5          4          bla        Pending

6          4          bla        Sold

 

The LINKID field is referenced to the first table,  so for each record of the first table there can be many records in the second.  So in the code you did the query and used the value of 4 it would pull 3 records.

 

Received

Pending

Sold

 

But somehow using code how to decifer which one is the highest value.  If for instance you did the query using the LINKID 2 you would get two records

 

Received and Pending.

 

Pending is the highest value.

 

The trick is how to to put a value to a string.  Or would you recomend some other relationship in the database? To reflect the values of each String

[todayme]

What about if I were to add a number to the end of each string like this.

 

Sold5

Received1

Pending2

Closed5

 

And then use some function to pull the last character out of the string work out what is the highest and assign the rest of the string to the variable I need to print on the page.  Would that make it easier?  Like I said I only under the logic, I programm but not in PHP.

[todayme]

Look why dont I just replace the values of

 

Sold

Closed

Pending

Received with numbers, how do we do it then.  This way maybe use an if statment at the end to say well if the number is 5 print this or if number is 1 print this.

 

So given that the values of the tables are now numbers how do we do it, surely we can just do a select case statement or some if statments to assign which number it is to the value string assigned to the number value.

 

 

[todayme]

One more thing just so its not to confusing.

 

Sold and Closed would not be equal values that would be to confusing

 

Sold would be 5

Closed = 6

Pending = 3

Received = 1

 

So the table above I have put in the number values instead of the string values.

[todayme]

Here is a start it is not correct but its a start, dont quite get the syntax but I know something is missing.

 

$query = "select max(*) from _Replies where LINKID = '$LeadID'";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result) or die(mysql_error());

foreach($row as $key => $val) {

     echo "The highest value is: ".$key." with a value of ".$val;

}

[sn33kyp3t3]

Not quite sure if you are asking for help with the logic, or with implementing a scale system for your status field.

 

The latter is simple, use an associative array like so:

 

$values = array('Sold'=>5, 'Closed'=>5, 'Pending'=>2, 'Recieved'=>1);

 

Then you could check the values while you loop through the records like so:

 

$lowest = 0;
$coolvalue = $lowest;

for(mysql_fetch_array($sql_events) as $row){
if( $values[$row['Status']] >= $lowest )
   $coolvalue = $values[$row['Status']];
}

 

Hope that helps.