How to echo all rows with certain aspect

Question

I have two tables. They are connected via a one (userinfo) to many (achievements) foreign key relationship. What I am attempting to do below is echo all the rows which have the given $usrid. This could be more than one.

Unfortunately, It only echos content one of the rows. How can I change it to echo all the rows where a a certain userid is present?

<!DOCTYPE HTML>
<head>
<?php  $usrid = $_GET['usrid'];
$connection = @mysqli_connect("localhost","root","","Rain")
OR die('Could not connect' .
      mysqli_connect_error());

$query = "SELECT usrid, username, oldname, languages, joindate, art, hunting, frontwebdev, backwebdev, writing, programming, se, smm, pentesting, timezone, availability, reliability, profilePicture FROM userinfo WHERE usrid='" . $usrid . "';";
$response = @mysqli_query($connection,$query);
$row = @mysqli_fetch_array($response);
$username = $row['username'];

$achvquery = "SELECT achieveid, usrid, achievementname, achievementdescr, timestamp FROM achievements WHERE usrid=" . $usrid . ";";
$achvresponse = @mysqli_query($connection,$achvquery);
$achvrow = @mysqli_fetch_array($achvresponse);
$achvtitle = $achvrow['achievementname'];
$achvdescr = $achvrow['achievementdescr'];
?>
<title>
All Achievements
</title>
</head>
<body>
<span> <?php echo "<span> " . $username . "s OD Achievement History "; ?> </span>

<span id="newAchvLink"> <?php echo "<a id='addNewLink' href='addachievement.php?usrid=" . $usrid . "'> Add new</a>"; ?></span>
<br /> <?php echo "<h2> Achv: </h2>  <h3 class='achvtitle'>" . $achvtitle . "</h3>"; echo $achvdescr;?><br /><br />
</body>
</html>

you'll need to continually fetch rows, use a `while` block https://stackoverflow.com/questions/12647154/mysqli-query-results-to-show-all-rows — Kevin, Jan 15 '18 at 06:56
Also, dont use the @ operator to prevent error messages. Plus your code is vulnerable to SQL injection.. — Manuel Mannhardt, Jan 15 '18 at 07:06
@ManuelMannhardt I know, I am testing this locally and plan to fix it later. That's not the problem right now. — dowo3, Jan 15 '18 at 07:09
@dowo3 why go back over your code to restructure it? Fix it now so you save yourself time when rolling out the sofrware — Daryl Gill, Jan 15 '18 at 07:26

score 0 · Answer 1 · answered Jan 15 '18 at 07:09

0

You can use while loop to print all row

$query = "SELECT usrid, username, oldname, languages, joindate, art, hunting, frontwebdev, backwebdev, writing, programming, se, smm, pentesting, timezone, 
 availability, reliability, profilePicture FROM userinfo WHERE usrid='" . $usrid . "';";
$response = @mysqli_query($connection,$query);
while ($row = @mysqli_fetch_array($response))
{
    echo $row['username'];
}

answered Jan 15 '18 at 07:09

omkara

974
5
24
50

Nothing is retrieved when i attempted this. The page remains blank – dowo3 Jan 15 '18 at 07:18
remove @ and try again – omkara Jan 15 '18 at 07:20
– dowo3 Jan 15 '18 at 07:23
I used the code above, (modified version of yous so it'd fetch the right info) it now only retrieves the last/most recent row (with the relevant id) – dowo3 Jan 15 '18 at 07:23
try to echo $query and run in phpmyadmin and see your query working properly or not – omkara Jan 15 '18 at 07:33

Billy · Answer 2 · 2018-01-15T07:28:17.433

0

Where you have the PHP echo statement you can replace it with something like...

 while($row = $result->fetch_assoc($response)) {
 echo "<span> " . $username . "s Op Achievement History ";

You should look at JOIN to simplify all of this for you. https://www.w3schools.com/sql/sql_join.asp

This is a pretty good/easy to understand usage of PHP Loops. https://www.tutorialspoint.com/php/php_loop_types.htm

edited Jan 15 '18 at 07:28

answered Jan 15 '18 at 07:14

Billy

126
1
10

OMKARA has a more in-depth answer. I would still take a look at learning the JOIN clause. – Billy Jan 15 '18 at 07:16

score 0 · Answer 3 · answered Jan 15 '18 at 07:53

0

first table query
while loop
{
$userid=first_table_data['user_id'];
2nd table query where userid=$userid
while loop
{

}

all value save in array
}
print value

answered Jan 15 '18 at 07:53

Mazhar Hussain

125
1
11

How to echo all rows with certain aspect

3 Answers3