PHP - To Join Or Not To Join

Hello everyone,   I am hopping someone can help me sort out a query.   I have two tables, one table is a players table, and a second table is a player_vitals data that stores heights and weight changes.  I want to list all the players showing the latest height and weight change.   this query will not fetch the latest height and weight   SELECT a.player_id, a.player_first_name,a.player_last_name, b.player_height, b.player_weight

First off Please bear with me. I am newbie.

Here is a table name 'Employes' and has following Columns.
ID(PK) | FirstName | LastName | SecurityLicence | CrowdLicence | DriversLicence | Password |

And through form all the values are assigned to these columns and user gets registered.
I have done it using this code and its working fine.
Code: [Select]
<?php
session_name('YourVisitID');
session_start();
if(!isset($_SESSION['FirstName']))
        {
                header("Location: http://" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php");
                exit();
        }
else
{
$page_title = 'Register';
include('templates/header.inc');
if(isset($_POST['submit']))
{
        require_once('mysql_connect.php');
        // connect to the db
        
        //Create a function for escaping the data.
        function escape_data($data)
        {
                global $con; // need connection
                if(ini_get('magic_quotes_gpc'))
                {
                        $data = stripslashes($data);
                }
                return mysql_real_escape_string($data, $con);
        }
        $message = NULL;
        if(empty($_POST['firstName']))
        {
                $fn = FALSE;
                $message .= '<p>you forgot to enter your first name!</p>';
        }
        else
        {
                $fn = escape_data($_POST['firstName']);
        }
        
        if(empty($_POST['lastName']))
        {
                $ln = FALSE;
                $message .= '<p>You forgot to enter your last name!</p>';
        }
        else
        {
                $ln = escape_data($_POST['lastName']);
        }
        if(empty($_POST['licenceId']))
        {
                $li = FALSE;
                $message .='<p>You Forgot enter your Security Officer Licence Number!</p>';
        }
        else
        {
                $li = escape_data($_POST['licenceId']);
        }
        if(empty($_POST['crowdLicenceNo']))
        {
                $cln = FALSE;
                $message .='<p>You Forgot to enter your Crowd Controller Licence Number!</p>';
        }
        else
        {
                $cln = escape_data($_POST['crowdLicenceNo']);
        }
        if(empty($_POST['driverLicenceNo']))
        {
                $dln = FALSE;
                $message .='<p>You forgot to enter your Driving Licence Number!</p>';
        }
        else
        {
                $dln = escape_data($_POST['driverLicenceNo']);
        }
        if(empty($_POST['password']))
        {
                $p = FALSE;
                $message .='<p>You forgot to enter your password!</p>';
        }
        else
        {
                        if($_POST['password'] == $_POST['password2'])
                                {
                                        $p = escape_data($_POST['password']);
                                }
                        else
                                {
                                        $p = FALSE;
                                        $message .='<p>Your password did not match the confirmed password</p>';
                                }
        }
        if($fn && $ln && $li && $cln && $dln && $p)
        {
                                
                $query = "SELECT ID FROM Employes WHERE SecurityLicence='$li'";
                $result = @mysql_query($query);
                if(mysql_num_rows($result) == 0)
                { 
                
                
                $query = "INSERT INTO Employes (FirstName, LastName, SecurityLicence, CrowdLicence, Driverslicence, Password)
                VALUES ('$fn', '$ln', '$li', '$cln', '$dln', PASSWORD('$p'))";
                $result = @mysql_query($query);
                        if($result)
                                {
                                        echo '<p>You have been registered</p>';
                
                                }
                        else
                        {
                                $message = '<p>We apologise there is a system error.</p><p>' . mysql_error(). '</p>';
                        }
                } 
                else 
                {
                        $message = '<p>That Security Licence is already registered</p>';
                }
                        mysql_close();
        }
        else
        {
                $message .='<p>Please try again</p>';
        }
}

//print the message if there is one
if (isset($message))
{
        echo '<font color="red">', $message, '</font>';
}
}
?>
<script type="text/javascript">
function validate_form()
{
        var f = document.forms["regForm"]["firstName"].value;
                if(f==null || f=="")
                        {
                                alert("First Name must be filled out");
                                return false;
                        }
        var l = document.forms["regForm"]["lastName"].value;
                if(l==null || l=="")
                        {
                                alert("Last Name must be filled out");
                                return false;
                        }
        var sl = document.forms["regForm"]["licenceId"].value;
        var s = /^\d{5,}$/g.test(sl);
        var sll = sl.length;
                if(s==false)
                        {
                                alert("Security Licence No must be filled out in digits");
                                return false;
                        }
                else if(sll>=7)
                        {
                                alert("Invalid Security Licence No");
                                return false;
                        }
        var csl = document.forms["regForm"]["crowdLicenceNo"].value;
        var k = /^\d{5,}$/g.test(csl);
        var csll = csl.length;
                if(k==false)
                        {
                                alert("Crowd Controller Licence No must be filled out in digits");
                                return false;
                        }
                else if(csll>=7)
                        {
                                alert("Invalid Crowd Controller Licence No");
                                return false;
                        }
        var d = document.forms["regForm"]["driverLicenceNo"].value;
        var v = /^\d{6,}$/g.test(d);
        var dl = d.length;
                if(v==false)
                        {
                                alert("Driver's Licence No must be filled out in digits");
                                return false;
                        }
                else if(dl>=11)
                        {
                                alert("Invalid Driver's Licence No");
                                return false;
                        }
       
}
</script>


<h3>Employment Registration Form</h3>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="regForm" method="post" onsubmit="return validate_form()">
<fieldset>
<legend>Enter informations in the form below</legend>
<table>
<tr><th>First Name:</th><td><input type="text" name="firstName" /></td></tr>
<tr><th>Last Name:</th><td><input type="text" name="lastName" /></td></tr>
<tr><th>SO Licence No:</th><td><input type="text" name="licenceId" size="5" /></td></tr>
<tr><th>CC Licence No:</th><td><input type="text" name="crowdLicenceNo" size="5" /></td></tr>
<tr><th>Driver's Licence No:</th><td><input type="text" name="driverLicenceNo" size="10" /></td></tr>
<tr><th>Create Password:</th><td><input type="password" name="password" size="10" /></td></tr>
<tr><th>Confirm Password:</th><td><input type="password" name="password2" size="10" /></td></tr>
</table>
<input type="submit" name="submit" value="Register" />
<input type="reset" value="Reset" />
</fieldset>   
</form>

<?php
include('templates/footer.inc'); 
?>
 

Here is another Table "Jobs", with the following columns.
JobID(PK) | ID(FK) | JobDate | JobStart | JobFinish | JobLocation | RequestedBy |

For Example I am the owner of the company and want to assign jobs to my registerd workers using a form.
I assign JobDate=12/12/12 JobStart=1900 JobFinish=2300 JobLocation=Perth Requestedby=John. And I do it using form. JobID will be incremented automatically.
Now what i dont understand is how do I assign ID(FK) to it?? I am newbie this question may sound stupid to many of you but hey please help.
Or should I replace ID(FK) with the SecurityLicence of the worker, Like this
JobID(PK) |ToSecurityLicence | JobDate | JobStart | JobFinish | JobLocation | RequestedBy |

What I want is when the workers signs in He should see the job assigned to him. Can I do it using inner join??? if so how to right PHP code for comparing these two tables???
I am way too much confused.

Too many of you I have made a fool of myself asking this question. But I believe a person who doesnt ask the question is fool foreva.

If somebody could Help I would really really aprreciate it.

Thanks.

Hi

I want to know I can select which ID I get from an inner join?

Not sure If I should post here or in the MYSQL forum, but as it's using OOP I posted here.


Code: [Select]
$sql = "SELECT * FROM area_county INNER JOIN area_country ";
$sql .= "ON area_county.country_id = area_country.id ";
$sql .= "ORDER BY area_country.country, area_county.county ASC ";
$sql .= "LIMIT {$per_page} ";
$sql .= "OFFSET {$pagination->offset()}";
$list = Area_county::find_by_sql($sql);

foreach($list as $lists){
$ID = $lists->id;
$country_id = $lists->country_id;
$county = ucwords($lists->county);
        $country = ucwords($lists->country);

echo $ID;
}


I want it to show the ID of the county, NOT the id of the country.
both tables in the database have the ID column called id.

I know this can be done, but not to sure how.


Thanks

"                                                                                        SELECT  event.*,
epr1.participantId AS team_1,
epr1.participantRoleId AS team_1_role,
epr2.participantId AS team_2,
epr2.participantRoleId AS team_2_role
  FROM  bb_EventParticipantRelation epr1,
   bb_EventParticipantRelation epr2,
bb_Event event
  WHERE 
   event.parentId='".$leage_type."' AND 
epr1.id != epr2.id AND
   epr1.parentParticipantId = '0' AND
epr2.parentParticipantId = '0' AND
epr1.eventId=event.id AND
epr2.eventId=event.id

ORDER BY epr1.participantRoleId ASC, event.startTime, team_1, team_2 ASC

"


This sql gets 2 rows and combines them as one, it does work but the issue is it seems to be taking too long in loading the page also I just need some advice on a better version


Thank you

I am somewhat of a newbie and think this is a simple fix, but I have been having problems and can't seem to figure it out. I am trying to JOIN two tables, but am not getting the results I would like. I am getting the correct question number displayed whenever I try different instances, but it always defaults to the "DTCAP" question (q_code).   I have included the snippet of code and how the table structure looks. Any help would be greatly appreciated!   -------------------------------------------------------------------------------------   //Testing to ensure that the $_SESSION['code'] is DTCAP, CNROW, or MONFW after user chooses option echo $_SESSION['code'];   $query_question=mysql_query("SELECT * FROM adventure_questions q
            INNER JOIN users_adventures u ON q.q_number = u.ua_question
            WHERE u.ua_username = '$_SESSION[username]' AND u.ua_code='$_SESSION[code=auto:0]'");   $question_info=mysql_fetch_array($query_question);   echo $question_info['q_number'].". ".$question_info['question'];   -------------------------------------------------------------------------------------   adventure_questions q_id     q_code       q_number     question 1          DTCAP              1         DTCAP question 1 2          DTCAP              2         DTCAP question 2 26       CNROW              1        CNROW question 1 27       CNROW              2        CNROW question 2 51       MONFW              1        MONFW question 1 52       MONFW              2        MONFW question 2   users_adventures ua_id    ua_username       ua_code      ua_question    ua_score 1                kjb31ca             DTCAP              1                   0 2                kjb31ca            CNROW              2                 10 3                kjb31ca            MONFW              1                  0
Edited by KjB31ca, 20 May 2014 - 06:39 AM.

Hi All, hopefully someone can lend me some insight with a problem I'm stuck with.

What I have is two tables.

TABLE: investmentOfferings
   id:            Unique identifier
   class:         Class of offering (A, or AA)
   year:         Year of offering
   name:         Friendly name of offering
   units:         Amount of units added in this offering
   dollarPerUnit:   Dollar value of one unit in this offering
   warrant:      Boolean for warrant or no (0=no 1=yes)

TABLE: actualInvestments
   id:          Unique identifier
   investor:      Identifies investor by referencing investors>id
   dollarAmount:   Dollar amount invested in this offering
   class:      Identifies class by referencing investmentOfferings>id

The structure of the application is so. investmentOfferings creates 4 fundraisers. Each year a new one. The actualInvestments table is the investment contributed per offering. Essentially what I need to do is to create a report, per investor(investors have a third separate table) that shows the amount invested per offering. The kicker being: not every investor will invest every offering, but I still needs to show a 0 dollar amount. I understand that I could create separate 0 dollar  investments for each investor, but that seems impractical. Especially where there will be more offerings once I'm finished working on this. In terms of the report, here's what I'm doing to output the data to a table.

Code: [Select]
<?php do {?>
  <tr>
    <td><?php echo $row_offerings['name']?></td>
    <td><?php echo $row_offerings['dollarAmount'];?></td>
<?php while ($row_offerings = mysql_fetch_assoc($offerings)); ?>
This creates a new column in the table for each part of my array. The issue I think is in my query.
Code: [Select]
"SELECT * FROM actualinvestments AS a RIGHT JOIN investmentofferings AS i ON i.id=a.offering WHERE class='AA' AND investor=" . $investorID . " OR investor IS NULL"Ive tried variations on this, but cannot seem to find anything that will produce currently entered investments, and placeholders for offerings with no investments. Could anyone steer me in the right direction? Any help would be greatly appreciated.

I am trying to join these 2 tables below, but its not working, any ideas where i am going wrong??

<?php
require("../include/mysqldb.php");
$con = mysql_connect("$dbhost","$dbuser","$dbpass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("$dbame", $con);



$result = "SELECT *
Reg_Profile_public.pref, Search_profiles_up.search_small_image
FROM
Search_profiles_up
INNER JOIN
Reg_Profile_public_Sex
ON
Search_profiles_up.UIN=Reg_Profile_public.UIN
WHERE
UIN='803272125132009'";

while ($row = mysql_fetch_array($result)) {
    echo $row['search_small_image'];
    echo $row['pref'];
}



?>

Hi guys,

Would like to check if I have done my INNER JOIN function correctly, as I have received and an error msg

Warning: mysqli_error() expects exactly 1 parameter, 0 given in D:\inetpub\vhosts\111.com\httpdocs\111.com\registration3.php on line 598.

Thanks


<?php
   $dbc = mysqli_connect('localhost', '111', '111', '111')
   or die(mysqli_error());
   $query = "SELECT sl.subject_level_id, sl.level_id, sl.subject_id, tl.name AS level_name, ts.name AS subject_name " .
    "FROM tutor_subject_level AS sl " .
    "INNER JOIN tutor_level AS tl USING (level_id) " .
    "INNER JOIN tutor_subject AS ts USING (subject_id) ";
   $sql = mysqli_query($dbc, $query);
   
   echo'<table><tr>'; // Start your table outside the loop... and your first row
   $count = 0; // Start your counter
      while($data = mysqli_fetch_array($sql)) {
         /*    Check to see whether or not this is a *new* row
            If it is, then end the previous and start the next and restart the counter.
         */
         if ($count % 5 == 0) {
            echo "</tr><tr>"; $count = 0;
         }
         echo '<td><input name="subject_level[]" type="checkbox" id="'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';
         echo '<label for="'.$data['subject_name'].'">'.$data['subject_name'].'</label></td>';
         $count++; //Increment the count
      }
   echo '</tr></table><br/><br/>'; //Close your last row and your table, outside the loop
   
?>


Wilson

Hi, well, I think it's about joining within the same table, but I'm not sure. Here's my problem:

I have a table with a number of categories and undercategories, see screendump of database he


I show the categories in my select by:

<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>

This gives me (here sorted alphabetically):
1.semester
3.semester
5.semester
Underkategori_1_i_1.semester
Underkategori_1_i_3.semester
Underkategori_1.1_i_3.semester

But I would like for it to show the path (the relation between categori and under-/subcategory (see "parent" and "path" in the database (screenshot above)), so that is will display the list like this:

1.semester
1.semester/Underkategori_1_i_1.semester
3.semester
3.semester/Underkategori_1_i_3.semester
3.semesterUnderkategori_1_i_3.semester/Underkategori_1.1_i_3.semester
5.semester

etc.

How can I join either "parent" or "path" with "id" (see screendump of database above) to create a listing like that? Or is that the correct way to do it at all?

Hope someone can help me out!

Hi,

Been looking for a little while on this problem, but can't seem to find a solution.

Ok, so I have two tables,

gallery & users

Gallery contains images, image info etc.
Users contains usernames, emails etc.

In users there is a 'photo' column, which references to an ID in the gallery table. This is so each user can have a 'profile' image from a bank of images stored in gallery.

What i'm trying to do is have a query that will output all of the images, and then show the usernames of assigned to each photo if there is one.

Heres what i've got:

Code: [Select]
$result = mysql_query("SELECT *,COUNT(photo) AS usercount FROM gallery LEFT JOIN users ON gallery.img_id=users.photo GROUP BY img_id ORDER BY date_uploaded ASC");
Its all fine, and displays the username, though if there are multiple users using the same photo, it only shows one username.
However, when displaying 'usercount', it will show that there are 2+ or whatever.

Short of nesting queries within other loops, is there an alternative solution??

Thanks!
Edd

Hello:

I am writing this code:
Code: [Select]
<?php

include('include/myConn.php');
include('include/myCodeLib.php');

$zip_id = $_REQUEST['zip_id'];

$query=mysql_query("SELECT zip_id,city,full_state,zip FROM zip_codes WHERE zip_id = $zip_id") or die("Could not get data from db: ".mysql_error());

while($result=mysql_fetch_array($query))
{
  $zip_id=$result['zip_id'];
  $city=$result['city'];
  $full_state=$result['full_state'];
  $zip=$result['zip'];
}

?>

<!DOCTYPE HTML>

<head>

<title></title>

</head>

<body>

<?php echo $city; ?>, <?php echo $full_state; ?> <?php echo $zip; ?>

</body>

</html>


It works fine as far as writing out the "City, State Zipcode", but I wanted to see if there is a way to "join" the 3 statements together so it can be written as just 1 statement. Like:
Code: [Select]
<?php echo $city, $full_state $zip; ?>

What is the trick to doing this?

Thanks in advance.

I need to get the friends password and insert it into the table so the friend can delete it later can some one help me do a table join
<?php
//include the connect script
include "../../../../connect.php";

//Post variables from flash
$username = $_POST['username'];
$password = $_POST['password'];
$status = $_POST['status'];
$friend = $_POST['friend'];

$username = stripslashes($username);
$password = stripslashes($password);
$status = stripslashes($status);
$friend = stripslashes($friend);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$status = mysql_real_escape_string($status);
$friend = mysql_real_escape_string($friend);

$sql = mysql_query("SELECT * FROM user_declined_list  WHERE username = '$username' and password = '$password' and friend = '$friend' and status ='$status'");
$rows = mysql_num_rows($sql);
$your_username=$rows['username'];



if($rows > 0)
{
echo "&msgTextFriendShipRejected= RESEND NEW FRIEND REQUEST SUCCESSFULLY!";

// ok they are now our friend but we are not thier friend so lets insert the friendship in reverse and set the status to 1 on both ends
// Problem i see is this if the user wishs to remove the friend later he will neen his password set to delete from this table.
// so i need to do a join table again where friend password gets inserted here..
$insertnewfriend = mysql_query("INSERT INTO user_friends_list (username,password,friend,status) VALUES ('$friend','$password','$username','0')") or die(mysql_error());
//ok lets update the request and set the friend to be a friend becuase we said yes be my friend.
$deleteuserfriend = mysql_query("DELETE FROM user_declined_list  WHERE username = '$username' and password = '$password' and friend = '$friend' and status ='0'");
return;
}


?>

Hello, I'm trying to get a join with 2 tables but cannot get it to work. It's a contentsystem with an ID for the author. Another table tells the name of the author. Now I want to join these things. This is what i've coded so far:

<?
$query = "SELECT rmnl_content.content_aid, COUNT(rmnl_content.content_id), rmnl_crew.crew_id, rmnl_crew.crew_name

FROM rmnl_content, rmnl_crew" "where rmnl_content.content_aid = rmnl_crew.crew_id";
   
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
   echo " <b>". $row['COUNT(rmnl_content.content_id)'] ."</b> posted messages by ". $row

['crew_rmnl.crew_name'] ."</td> .";
   echo "<br />";
}
?>

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=332358.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=320476.0