Show only some value from column in database with PHP code

Question

I have 5 tables in database. Database is for statistics for football (soccer) players and its relational database.

One is for table is for player, second for club, thrid for season, fourth tables is for player stats, and fifth table is connecting club and season.

I done everything is working like perfect except one thing. I want to paste values from database on site. In example that I gonna give that is season 1923, and that season has 6 clubs in league. On bottom page I have part of page that I am going to show statisicts for players (first name, last name, apps and goals for that season). And its working but not how I want. Its echoing all players and I want to divide that players first to be first club and his players then second and his...etc to the 6. Something like this:

club. 
CLUB 1
players from that club with statiscits for that season
CLUB 2
...
etc

How this can be done with PHP, or I must alter my sql query. I can retrieve id of Club and name of Club but its not working with If else?! This is php code

<?php
    include('connect-mysql.php');
    $sqlcom = "SELECT jos_players.firstName, jos_players.lastName,jos_players.playerpic, jos_playerstats.idClub, jos_playerstats.idSeason, jos_igraciDB_club.name, jos_playerstats.apps, jos_playerstats.goals, jos_players.idPlayer
    FROM `jos_playerstats` 
    LEFT JOIN `jos_igraciDB_club` ON `jos_playerstats`.`idClub` = `jos_igraciDB_club`.`idClub` 
    LEFT JOIN `jos_igraciDB_season` ON `jos_playerstats`.`idSeason` = `jos_igraciDB_season`.`idSeason` 
    LEFT JOIN `jos_players` ON `jos_playerstats`.`idPlayer` = `jos_players`.`idPlayer` 
    ORDER BY CASE position
    WHEN 'Vratar' THEN 1
    WHEN 'Bek' THEN 2
    WHEN 'Half' THEN 3
    WHEN 'Navala' THEN 4
    END";

    $result = mysql_query($sqlcom);
    while($row=mysql_fetch_assoc($result))
    {
     $fname = $row['firstName'];
     $lname = $row['lastName'];
     $nick = $row['nickName'];
     $apps = $row['apps'];
     $goals = $row['goals'];
     $position = $row['position'];
     $img = $row['playerpic'];
     $idpl = $row['idPlayer']; 

     echo '{modal igraci/article/'.$idpl.'|width=500|height=400|title='.$lname.' '.$fname.'}'.$fname.' '.$lname.' ('.$apps.'/'.$goals.'){/modal}   '; 

      }
?>

Alexander · Accepted Answer · 2015-04-19 16:46:59Z

1

question related

In your reply to this question you fire up the query three times. You can simplify your code by firing it once only. Further you may iterate through $result1 for each club. You can reset the pointer - at least mysqli supports it.

recommend switch

Instead of using mysql which is deprecated I recommend to use mysqli or pdo.

edited Apr 19, 2015 at 16:46

answered Apr 19, 2015 at 16:40

Alexander

5011 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mors Violenta Over a year ago

Thanks, I gonna consider switiching to mysqli...Please just add to comment how you reset the pointer in mysqli for this iteration? reset to null or?!

Alexander Over a year ago

Based on the php manual you have to call data_seek function and pass 0. while($row = $result1->fetch_row()) {} $result->data_seek(0);

Mors Violenta · Accepted Answer · 2015-04-19 14:55:41Z

I managed to find solution with separate while loops for each team. This is the solution, if some has better, easier solution without too much while loops please post here. This is example for three clubs:

$result1 = mysql_query($sqlcom);
$result2 = mysql_query($sqlcom);
$result3 = mysql_query($sqlcom);
while($row=mysql_fetch_assoc($result1))
{
if ($row['idClub'] == 2)
{
echo '{modal igraci/article/'.$row['idPlayer'].'|width=500|height=400|title='.$row['lastName'].' '.$row['firstName'].'}'.$row['firstName'].' '.$row['lastName'].' ('.$row['apps'].'/'.$row['goals'].'){/modal} ; '; 
}
}while($row=mysql_fetch_assoc($result2))
{
if ($row['idClub'] == 14)
{
echo '{modal igraci/article/'.$row['idPlayer'].'|width=500|height=400|title='.$row['lastName'].' '.$row['firstName'].'}'.$row['firstName'].' '.$row['lastName'].' ('.$row['apps'].'/'.$row['goals'].'){/modal} ; '; 
}
}
while($row=mysql_fetch_assoc($result3))
{
if ($row['idClub'] == 4)
{
echo '{modal igraci/article/'.$row['idPlayer'].'|width=500|height=400|title='.$row['lastName'].' '.$row['firstName'].'}'.$row['firstName'].' '.$row['lastName'].' ('.$row['apps'].'/'.$row['goals'].'){/modal} ; '; 
}
}

Collectives™ on Stack Overflow

Show only some value from column in database with PHP code

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related