Showing Data for Multiple Tables

ShaneTFletcher · April 13, 2012, 12:48pm

Hey all, this has been annoying me for a bit now and to be honest, Google isn’t really helping me understand what I actually need to do.
First, let me tell you about the website I am making. The point of the website is to make people keep a record of what jobs they have applied for. Simple!
Sooooo, my problem!

Basically, the code below displays information from a database called job_db and a table called jobs.
The jobs table consist of the following columns -
id, job_title, job_business, date_applied and end_date.

///////////////////////////////////////////// coding \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

<?php include "header.php"; ?> <?php // Connect to server and select database. mysql_connect("localhost", "root", "")or die("cannot connect"); mysql_select_db("job_db")or die("cannot select DB"); // select record from mysql $sql="SELECT * FROM jobs"; $result=mysql_query($sql); ?>

<?php while($rows=mysql_fetch_array($result)){ ?> <?php

// close while loop
}

// close connection;
mysql_close();

?>

	All Applied Jobs
ID	Job Title	Job Business	Date Applied	End Date	User
<?php echo $rows['id']; ?>	<?php echo $rows['job_title']; ?>	<?php echo $rows['job_business']; ?>	<?php echo $rows['date_applied']; ?>	<?php echo $rows['end_date']; ?>	<?php echo $rows['username']; ?>	Delete?

<?php include "footer.php"; ?>

The column “

<?php echo $rows['username']; ?>” should be from the other table I have called admin which consists of -
id, username, password, email, last_log_date.

However, I do not know how to add this table into my coding and view the user.

Can anyone help me here?

sarthakpatel · April 14, 2012, 5:26am

hello ShaneTFletcher, you are trying to display data from two table than you need to define a relation between those two table so by using join query you can display both table data in proper manner.
in your case i think you have to add one more field to you jobs table called “userid” this contain the value of id field of admin table

ex. admin table
id username password email last_log_date
1 demo demo123 [email protected] 2012-04-13

ex. jobs table
id job_title job_business date_applied end_date userid
1 developer IT 2012-04-10 2012-04-30 1

your query
“SELECT jobs., admin. FROM jobs, admin where jobs.userid = admin.id”

i hope this will helpful for you
SR

ShaneTFletcher · April 14, 2012, 8:54am

Thank you so much. That’s exactly what i needed.

Now it makes sense so thank you for making it more clear for me.

Thanks again

sarthakpatel · April 16, 2012, 5:38am

hello ShaneTFletcher,
it’s nice to hear that your problem is resolve.
post more queries and issues you will get proper and good answer from here.
SR

sarthakpatel · April 17, 2012, 6:00am

hello ShaneTFletcher,

For inserting user id into your jobs table in database than you need pass user id pass as hidden.
Tell me one thing only logged user can post a job correct?
if yes than you can get all detail of user who is logged in by session or some other ways.
so you just need to add a hidden field in your job post form like this
$userid = 1; //this variable contain the id of logged user so replce this value with logged user id.

do same as you are doing now just add one more field and value in you insert query.

I hope this will helpful for you.
post your issue here so you can get more response from other users too as well as me.
SR

ShaneTFletcher · April 17, 2012, 7:38am

Hi Sarthak,

The website is missing a huge part of information which is needed to even consider doing a log in website.
Basically, the website was a basic job input database where only me will input data using WAMP. However, after time coding it I wanted to allow my fellow local users to use this. So I added a very, very simple log in where it allows a user to input there username and password and done! They had full access to the website. Nothing special, nothing fancy!
Then it came to mind that what if I uploaded the website publicly to allow any user to sign up, sign in and add there jobs. This is where I am now and to be honest, its not coded to do this!

Now, the problem which is happening is -
when a user signs in to my login.php page and if everything is correct, it will log in and direct to a index.php page.
After this, there is no more information about which id is logged in and which is logged out.
This is now the issue, when I try to output the user who added the job, it will say nothing because there is apparent no user logged, just someone accessing the website as normal.

I can give you my login.php coding but lets be honest, I think all the coding will need to be changed.

Login.php coding

<?php session_start(); if (isset($_SESSION["manager"])) { header("location: index.php"); exit(); } ?> <?php // Parse the log in form if the user has filled it out and pressed "Log In" if (isset($_POST["username"]) && isset($_POST["password"])) { $manager = preg_replace('#[^A-Za-z0-9]#i', '', $_POST["username"]); // filter everything but numbers and letters $password = preg_replace('#[^A-Za-z0-9]#i', '', $_POST["password"]); // filter everything but numbers and letters // Connect to the MySQL database include "connect_to_mysql.php"; $sql = mysql_query("SELECT id FROM admin WHERE username='$manager' AND password='$password' LIMIT 1"); // query the person // ------- MAKE SURE PERSON EXISTS IN DATABASE --------- $existCount = mysql_num_rows($sql); // count the row nums if ($existCount == 1) { // evaluate the count while($row = mysql_fetch_array($sql)){ $id = $row["id"]; } $_SESSION["id"] = $id; $_SESSION["manager"] = $manager; $_SESSION["password"] = $password; header("location: index.php"); exit(); } else { header("location: login_error.php"); exit(); } } ?>

<?php include_once("login_header.php");?>

Log In

User Name:

Password:

     <input type="submit" name="button" id="button" value="Log In" />
   
  </form>
  <p>&nbsp; </p>
</div>
<br />

Thanks