Welcome, Guest: Join Nairaland / LOGIN! / Trending / Recent / New
Stats: 2,512,203 members, 5,728,709 topics. Date: Wednesday, 15 July 2020 at 12:50 PM

Please How Do I Print This Data From MY Database. MYSQL, PHP - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / Please How Do I Print This Data From MY Database. MYSQL, PHP (1707 Views)

I Need To Deal With My Database. / Free Online Training For Html/css, Javascript, Jquery, Mysql, Php, Java, Android / Problem Trying To Connect To My Database (2) (3) (4)

(1) (Reply) (Go Down)

Please How Do I Print This Data From MY Database. MYSQL, PHP by Thirdwrist(m): 3:39am On Dec 29, 2016
Please fellow programmers, I have been going through this wahala on how to print data from database in a specific way:

I have four tables i want to print data from simultaneously: memories, comment, users and likes, All at the same time . The issues is that i don't know what proper command to use, I have read joins but its either i dont understand or it cant be used for this measure.

this is what i want to do:
There a couple of data in the memories, i want to display each memories and with its comments, and the person who posted it, with the number of likes it received, all on the same page.
and the above repeat again on the same page.
Please I need help. Biko

Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by paranorman(m): 4:18am On Dec 29, 2016
MySQL joins will solve your problem. Take your time and learn simple join examples from W3school website.
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 9:45am On Dec 29, 2016
can you upload the codes so as to study the code?
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by Craigston: 12:34pm On Dec 29, 2016
You must be doing something wrong if you have four tables for that. Can you describe the structure of each table so we can know what relationships exist between your data entities?
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 1:23pm On Dec 29, 2016
Craigston:
You must be doing something wrong if you have four tables for that. Can you describe the structure of each table so we can know what relationships exist between your data entities?

without what he said, no help can be rendered...

1 Like

Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by Thirdwrist(m): 2:45am On Jan 01, 2017
onedayatime:


without what he said, no help can be rendered...

Here are the codes, thanks for the above info, I have made some improvement on my database after the analysis above... I am really grateful, but though the issues still persists.


<?php
session_start();
require "pages/user_management/dbconnect.php";
$sql="SELECT bio FROM bio WHERE bio_author = '{$_SESSION['user_id']}'";
$result=mysql_query($sql);
if($result==true){
while($row=mysql_fetch_array($result,MYSQL_ASSOC)){
$bio=$row['bio'];
$bio_value=1;
}
}
else{
$bio_value=0;
$bio="Please, Put up a bio.";
}
$sql="SELECT * FROM memories WHERE m_author= '{$_SESSION['user_id']}'";

$result=mysql_query($sql) or die("script failed"wink;

if($result==true){
$memories_value=1;
}

else{
$memories_value=0;
$memories="Please, Put up your memories from GSTC.";
}


?>

<!doctype html>
<html lang="en">
<link href="css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="custom.css">
<link rel="stylesheet" type="text/css" href="css.css">
<meta charset="utf-8">
<title> Profile</title>
<body style="background-color: #d3d3d3;">
<div class="row" style="margin-top: 15px;">
<div class="container">
<div class="col-lg-12" style="background-color: #dcdcdc;">
<div class="row"><div class="col-lg-12" style="background-color: #ffffff;" background-image="images/gstosa1.jpg" >
<div class="col-lg-3" id="div_left">
<div class="col-lg-12" id="profilepic_box">
<img src="images/profilep1.jpg" class="img-responsive" id="profilepic" ></div>
<div class="col-md-12" id="smallbox_left">
Set:
</div>
<div class="col-md-12" id="smallbox_left">
Department:
</div>
<div class="col-md-12" id="smallbox_left">
Date of Registration:

</div>
</div><div class="col-md-offset-4" id="div_right">
<div class="col-md-12" id="largebox_right1">
<h4 id="Pname"> Obinna Akunne</h4>
<small id="Pposition">Publicity Secretary</small>
</div>
<div class="col-md-12" id="largebox_right">
<div class="col-md-2" id="smallbox_right"> Bio</div>
<div class="col-md-10">
<?php
if($bio_value==0){
$task_action="bio";
echo $bio;
echo '<form role="form" action="task_master.php" method="post">
<div class="form-group">
<textarea class="form-control" rows="3" name="bio"></textarea>
</div>
<input type="submit" value="Publish"/>
</form>';
}
else{
echo $bio;
}
?>

</div>
</div>
<div class="col-md-12" id="largebox_right">
<div class="col-md-2" id="smallbox_right">Memories</div>
<div class="col-md-10">
<?php
if($memories_value==0){
echo $memories;
echo '<form role="form" action="task_master.php" method="post">
<div class="form-group">
<textarea class="form-control" rows="3" name="memories"></textarea>
</div>
<input type="submit" value="Publish"/>
</form>';
}
else{
while($row=mysql_fetch_array($result,MYSQL_ASSOC)){
echo $row['memories'].
'<div class="col-md-12" id="profile_like_tab">
comment count and likes
</div>'. " </br> ".
'<div class="col-md-12" id="profile_like_tab">

<?$sql= "SELECT * FROM comments WHERE article_id= 3";
echo $sql;
$result=mysql_query($sql);
if ($result==true){
$numrolls= mysql_num_rows($result);
;
}?>'
.'</div>'. '</br> ';


}


}
?>
<!--section for comments-->
<div class="col-md-12" id="comment_block">

<form role="form" action="task_master.php" method="post">
<div class="form-group">
<input type="text" class="form-control input-sm-8" name="m_comment"></textarea>
</div>
<input type="submit" value="Go"/>
</form>
</div>

<div class="col-md-12" id="comment_block">

<form role="form" action="task_master.php" method="post">
<div class="form-group">
<input type="text" class="form-control input-md-12" name="memories"></textarea>
</div>
<input type="submit" value="Publish"/>
</form>
</div>
</div>

</div>
</div></div>
</div>
</div>
</div>
</body>

</html>


The new comments table is attached below...

Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by Thirdwrist(m): 2:48am On Jan 01, 2017
paranorman
onedayatime
Craigston
onedayatime
Dhtml18
dhtml
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 9:23am On Jan 01, 2017
it is the sql code that you should upload. It might be that the tables are not well structured and if well structured, I can help you on how to retrieve from the tables. You will still use join
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by Craigston: 10:57am On Jan 01, 2017
onedayatime:
it is the sql code that you should upload. It might be that the tables are not well structured and if well structured, I can help you on how to retrieve from the tables. You will still use join
Yea, we need to see his actual queries.
Also try doing this, Thirdwrist:
Go to your mysql executable, run it in a console, login as the appropriate user and select the database you're using. Then for each table in the database, execute the SQL statement
"DESCRIBE tableName; > tableName_structure.txt"

That will save the structure of each table you specify to the corresponding TXT file. Post the output of each file here, together with your SQL queries. That will make your problem much easier to solve.
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by paranorman(m): 2:00pm On Jan 01, 2017
I don't know if your tables are properly normalized,
But if you wanna extract data from four tables, you needa use Sql joins.

If you can't post the tables' structures here, post the tables and how the are related, I mean the primary keys in a table and the corresponding foreign key in a related table.


I will check out your code when I get on PC
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by Thirdwrist(m): 2:06pm On Jan 01, 2017
Well the sql is in two parts:
the first one pulls out the data for all memories stored by one user:
SELECT * FROM memories WHERE m_author= '{$_SESSION['user_id']}'

And the second attempts to retrieve all comments that individually belongs to those
memories above:SELECT * FROM comments WHERE article_id = 3;

Please how do I go bout it, so i can print each post(memories) with its own comment below

Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 8:22am On Jan 02, 2017
you've not shown us the real mysql code you use..you are only showing us the one mix with php. that only access the table.

Do what craigson said you should do
Craigston:

Yea, we need to see his actual queries.
Also try doing this, Thirdwrist:
Go to your mysql executable, run it in a console, login as the appropriate user and select the database you're using. Then for each table in the database, execute the SQL statement
"DESCRIBE tableName; > tableName_structure.txt"

That will save the structure of each table you specify to the corresponding TXT file. Post the output of each file here, together with your SQL queries. That will make your problem much easier to solve.
you've not shown us the real mysql code you use..you are only showing us the one mix with php. that only access the table.

Do what craigson said you should do.
Or in phpMyAdmin click on the database you are working on and then click on export on the navigation. After exporting, you can upload the codes here.

P.s. Mentioning names dosent mean users whose name you mentioned will solve it for you. What if non of them is online? What if I don't even visit the programming forum? Or I visit and I don't even click on your post? Just post your question and whoever wanna help you will help
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by daPretender: 9:40am On Jan 02, 2017
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 10:02am On Jan 02, 2017
no light for my area o. My lapi ba3 is down and d pix is no sharp
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 10:05am On Jan 02, 2017
better still, write out the names of the tables and the fields and columns under each table
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by kudaisi(m): 11:21am On Jan 03, 2017
I'm not really sure what you want, but I made a rough code based on what you've described and I hope it meets your requirement. Attached is a screenshot of the resulting output looks like and here is the code:
<html>
<head><title>Memories</title>
<style>
.memory-comment,.memory-content{display:block;padding:9.5px;font-size:13px;color:#333}.memory-content{margin:0 0 10px;background-color:#ABD0BC;border:1px solid #ccc;border-radius:2px}.memory-comment{margin:10 0 10 40px;background-color:#f5f5f5;border:1px solid #ccc;border-radius:2px}.memory-author{color:#000;background:#DCF2B8;border:1px solid #9EC068;padding:2px 6px;margin:6px 0}#container{margin:0 auto;width:640px}
</style>
</head>
<body>
<div id="container">
<?php
$db = new mysqli('localhost', 'root', '', 'memory');

if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}
$sql = "
SELECT m.id,m.content, m.time,
concat(u.first_name,' ', u.last_name) as author
FROM memories m
LEFT JOIN user u ON u.id = m.author_id";

if(!$memories = $db->query($sql)){
die('Unable to run Query "' . $db->error.'"' );
}
// Write all memories with author name
// Note: From the query the user's first name and last name is included with a join and concatenated as author
while($row = $memories->fetch_assoc()){
echo "<div class='memory-content'>".$row['content'] . " by <span class='memory-author'>".$row['author']."</span></div>";
// For each memory in the loop find its comments
$sql = "SELECT * from comments WHERE memory_id =".$row['id'];
while($comment = $comments->fetch_assoc()){
echo "<div class='memory-comment'>".$comment['content']."</div>";
}
}
?>
</div>
</html>


Disclaimer: Use the code at your own risk.

Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by Nobody: 7:55pm On Jan 04, 2017
it seem to me that d front-end web app is a forum site. and i got d feeling dat d database.name table was never created with specified class functions that dispays dz information in rows for each users, but clustered abt in 4 separate tables. in cases lyk dz a single container table wuld have bin inplemented in the first place to specify each user with their attributes organised neatly in multi-rows. i m curious to knw if u av solved d abv problem till now? well, u are still going to code a mysql query syntax to JOIN the tables. and since u want to merge the four tables into one, it can be tricky n confusing but knowing ur primary key and foreign key will make the work much easier. meaning to really get d whole wrap upstairs, u shuld first join tb1 to tb2 to form tb2, then join tb2 to tb3 to form tb3 then finally join tb3 to tb4 to form 4tb? i knw its confusing but JOIN or subquery is d only way. or u jst first identify a link-register tha wuld act as d foreign key among all d four data tables.
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by onedayatime(m): 8:04am On Jan 05, 2017
Coin2naira:
it seem to me that d front-end web app is a forum site. and i got d feeling dat d database.name table was never created with specified class functions that dispays dz information in rows for each users, but clustered abt in 4 separate tables. in cases lyk dz a single container table wuld have bin inplemented in the first place to specify each user with their attributes organised neatly in multi-rows. i m curious to knw if u av solved d abv problem till now? well, u are still going to code a mysql query syntax to JOIN the tables. and since u want to merge the four tables into one, it can be tricky n confusing but knowing ur primary key and foreign key will make the work much easier. meaning to really get d whole wrap upstairs, u shuld first join tb1 to tb2 to form tb2, then join tb2 to tb3 to form tb3 then finally join tb3 to tb4 to form 4tb? i knw its confusing but JOIN or subquery is d only way. or u jst first identify a link-register tha wuld act as d foreign key among all d four data tables.
you can do what he says or write the names of the tables and its field.
E.g.
table comments: id, article_id, etc.
table article: id, likes, mention etc.
Other tables as well.
Re: Please How Do I Print This Data From MY Database. MYSQL, PHP by interjames: 10:45am On Jan 05, 2017
He needs a very straight and simple answer lets not complicate the whole issue, take your time to learn everything about SQL queries then you can manipulate as much as you can

(1) (Reply)

I Need To Learn Programming Seriously / Wadup Messenger (beta)test Version Is Live!!! / Rivers State University Annual Jean Carnival

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2020 Oluwaseun Osewa. All rights reserved. See How To Advertise. 150
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.