Match data before displaying it on the web. 1

[Cimm]

I have a little problem that I am not sure how to do it.

I have a website, which has 2 tables with information that I am interested in.

When I type in index.php?memberid=4

It will show information regarding this member with ID 4.

I have a another table I also would like to display in this section. The only common data that matches from these tables is the name.

member table looks like this

ID, NAME ......

standing table looks like

ID, Corp1 , corp2 .....

Name & Corp1 is identical

Any suggestions?
Thanks in advance
Mike

 

[jpadie]

you should use the primary key of a table as the foreign key in another table. what happens, for example, if the name of a member changes? You would have to go through the standing table and change the value of Corp1 to match the new name, which may then have knock on effects on the data integrity of other tables in the database.

but to answer your question, you can either do two queries (the first to pull in data from the members table from where you can extract the name; the second using the name on the standing table). or you could consult the mysql forum for the correct syntax for a join and retrieve your data in a single query.

 

[Cimm]

Ok thanks for the reply.

The member name cannot be changed, and as far as concern the standing table, it will be quite big and wont be affected by any changes in the member table.

All I want to do its just add additional information for display member site.

I'll ask around in mysql forum as well to see what could be done. It would be nice with a single mysql query to get the result.

 

[Cimm]

is this something you could help me with then jpadie?

//----------------------------
// Members table *SHOW STANDINGS*
//----------------------------

$maxRows_standingtable = 1000;
$pageNum_standingtable = 0;
$startRow_standingtable = $pageNum_standingtable * $maxRows_standingtable;
mysql_select_db($dbname, $connection);
$query_standingtable = "SELECT corp2, standing FROM ".TABLE_PREFIX."standings, ".TABLE_PREFIX."members WHERE (".TABLE_PREFIX."standings.corp1 = ".TABLE_PREFIX."members.name) AND ".TABLE_PREFIX."members.id =".$GETID." AND ".TABLE_PREFIX."standings.standing = 'Positive'";
$query_limit_standingtable = sprintf("%s LIMIT %d, %d", $query_standingtable, $startRow_standingtable, $maxRows_standingtable);
$standingtable = mysql_query($query_limit_standingtable, $connection) or die(sqlerror());
$row_standingtable = mysql_fetch_assoc($standingtable);

As you see I figured out how to do the join as you suggested. Now, a minor problem there could be several lines that match the ID that has Positive settings towards that particular member. I am thinking about making a loop of all this.

Any idea's how to implement that in the above code?

 

[Cimm]

Just to add, this is the code I have in my disp_member.php to browse the above code that is being called everytime disp_member.php is being executed.

<tr>
<td class="row2" valign="top"><b><?php echo $row_standingtable['corp2'] ?></b></td>
<td class="row1"><?php echo $row_standingtable['standing'] ?></td>
</tr>

I need it repeat and fill up more potential data.

 

[jpadie]

at first glance you need to replace this line

$row_standingtable = mysql_fetch_assoc($standingtable);

with this

$class= "";
while ($row_standingtable = mysql_fetch_assoc($standingtable)){
 $class= ($class==="evenrow")?"oddrow":"evenrow";
echo <<<EOL
<tr class="$class">
   <td class="col1" valign="top"><b> {$row_standingtable['corp2']}</b></td>
   <td class="col2">{$row_standingtable['standing']}</td>
</tr>

EOL;
}

 

[Cimm]

I am little lost,

The code I showed you
//----------------------------
// Members table *SHOW STANDINGS*
//----------------------------

is core.php


disp_member.php has the code
<tr>
<td class="row2" valign="top"><b><?php echo $row_standingtable['corp2'] ?></b></td>
<td class="row1"><?php echo $row_standingtable['standing'] ?></td>
</tr>

Looks to me that the code you typed out is for core.php but also for disp_member.php.

 

[Cimm]

Nevermind, your code is correct!
Awesome!.

Thanks

 

[jpadie]

how are you getting the variables from core.php to disp_members.php? i assumed you were just "include()"'ing it at the end of your database calls. if so then my post stands. delete the last line of the core.php and replace the table row with the code that i posted (in php tags i guess).

 

[Cimm]

I left $row_standingtable = mysql_fetch_assoc($standingtable); in core.php file.

Replaced the
<tr>
<td class="row2" valign="top"><b><?php echo $row_standingtable['corp2'] ?></b></td>
<td class="row1"><?php echo $row_standingtable['standing'] ?></td>
</tr>

in disp_member.php file

with your code. Works great. And yes, I had to add php tag's

 

[jpadie]

i suspect that you will be missing your first database value that way. the mysql_fetch_assoc call iterates through the recordset so the first call outside the while loop will move the pointer on to the first record and then when you hit the while call you move on to the second before you are actually outputting any data. if you need to do it that way then change the while (expression){} to do {} while(expression)

good luck

 

[Cimm]

Yes, you are right.

I didnt take me long til I figured out something was not right. I deleted the line in the core.php file. Looks better now.

I appreciate the help. I am just having some terrible issue with how the tables is getting on each other. Looks like a inverted pyramid


//Mike

 

[jpadie]

feel free to post some source output if you want help. have you thought of replacing the table structure with css or is it genuinely tabular data that you are trying to display?