PHP - Inner Join Question

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.

I'm an amateur PHP/mySQL person, trying to learn some more things, and I've hit a snag trying to do something I haven't done before.

Okay, here's what I'm trying to do. I have 3 tables:

Items (which includes `id` as the primary key) and a TON of information about each item.
Shop (which includes `id` as the primary key) and shop name and location.
Sale which has shop_id and item_id.

So, what I'm trying to do is have a form where someone can input in an item name and a shop name and have that item then added to the shop. This is where the Sale table comes into play too, as that's going to house each of the items and what shop it's in.

I currently have the following queries:


       $query5 = $db->execute("SELECT id FROM items LEFT JOIN sale ON items.id=sale.item_id");
       $query6 = $db->execute("SELECT id FROM shop LEFT JOIN sale ON shop.id=sale.shop_id");


I have the following form:


        if ($name == "") { $errors++; $errorlist .= "Name is required.<br />"; }
        if ($location == "" ) { $errors++; $errorlist .= "Location is required.<br />"; }
if (!isitem($itemname)) die("That item does not exist."); }

        if ($errors == 0) { 
            $query = doquery("INSERT INTO `sale` SET `item_id`=`$item`");
            admindisplay("New Item Added.","Add Items");

$page = <<<END

<b><u>Add Item</u></b><br /><br />
<form action="create_shop.php?do=additem" method="post">
<table width="90%">
<tr><td width="20%">Name:</td><td><input type="text" name="itemname" size="30" maxlength="255" value="" />**Be sure the item is already in the database and matches it IDENTICALLY.</td></tr>
<tr><td width="20%">Location:</td><td><input type="text" name="location" size="30" maxlength="55" value="" /><br /><span class="small">Where is the item obtained (unique shop name, please!)</span></td></tr>
</table>
<input type="submit" name="submit" value="Submit" /> <input type="reset" name="reset" value="Reset" />
</form>


What next steps do I need to take? Am I even on the right path?

Thanks for any and all help anyone provides!! I really appreciate it!

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

I'm trying to pull from my database every spell or attack that is equal to `All`, but it doesn't seem to be working. All it is doing is pulling all the spells, regardless of class = `All`, and also 0 of the attacks. It should be producing a single attack that is = to `All` as no spells are = `All`.

Any help would be greatly appreciated! Thanks! (This is my first attempt at a JOIN statement...)


$query = "SELECT attacks.id, attacks.name, attacks.price, attacks.class, attacks.descript, spells.id, spells.name, spells.price, spells.class, spells.descript ".
"FROM attacks, spells ".
"WHERE attacks.class = 'All' || spells.class = 'All' order by attacks.name, spells.name asc";

I'm trying to figure out the best way to do this if I'm doing it right with my for loop. With how many numAnswers there are for the selected poll its going to put the div with the label and text box for each of those but its giong to go and tie in each of those pollAnswers with what ID of the pollAnswer to the pollAnswer table.


Code: [Select]
$pollsQuery = "
    SELECT
        polls.question,
        polls.statusID,
        polls.numAnswers,
        DATE_FORMAT(polls.dateExpires, '%m/%d/%Y') AS dateExpires
    FROM
        polls
    WHERE
        polls.ID = '" . $pollID . "'";
$pollsResult = mysqli_query ( $dbc, $pollsQuery ); // Run The Query
$row = mysqli_fetch_array ( $pollsResult, MYSQL_ASSOC );

$pollAnswers = "
    SELECT
        pollAnswers.ID,
        pollAnswers.answer
    FROM
        pollAnswers
    WHERE
        pollAnswers.pollID = '" . $pollID . "'";
$pollAnswersResult = mysqli_query ( $dbc, $pollAnswers ); // Run The Query

Code: [Select]
<fieldset class="answerLeg">
        <legend>Edit Poll Answers</legend>
        <?php for ( $j = 0; $j <= $numAnswers; $j++) { ?>
            <div class="field required answers">
                <label for="answer<?php $j?>">Answer <?php $j?></label><input type="text" class="text" name="answer<?php $j?>" id="answer<?php $j?>" title="Answer <?php $j?>"/>
                <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>     
            </div>
        <?php } ?>
    </fieldset>

I am using the following sql code to display a stream of statuses, I have the username/profile picture/status/likes working but I am now stuck on comments.

SELECT s.*, u.*, c.*, COUNT(l.likes_location_id) AS likeCount
FROM stream AS s
LEFT JOIN users as u ON (u.users_username = s.stream_username)
LEFT JOIN comments AS c ON ( c.comments_location_id = s.stream_id )
LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
ORDER BY s.stream_id DESC LIMIT 50

If a status has multiple comments the results only shows the first comment that was made. How can I get it to include all of them?   Also, how would I include this in the PHP loop, without having another loop?

Thanks,
Edited by slj90, 06 January 2015 - 08:56 PM.

I have a database with two tables, they are designed like this

Table1 has two fields (log_number, name)
Table2 has two fields (log_number, city)

I would like to do a search for a specific log number and display only one result for that matching log number (that we searched for) and display something like this 

Log Number: 59
Name: Mark
City: Chicago

this is my code so far


my search form

Code: [Select]
<form method="get" action="search.php" />   
      Log Number
     <input type="text" id="log_number" name="log_number" />
     <input type="submit" name="submit" value="Search" />

the search.php file

Code: [Select]
      <?php
 require ('/sqlconnect.php');
  $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
 
        $log_number = $_GET['log_number'];
        $name = $_GET['name'];
        $city = $_GET['city'];
 
  $query = "SELECT * FROM table1, table2 WHERE table1.log_number = table2.log_number";

  $result = mysqli_query ($dbc, $query);
     while ($row = mysqli_fetch_array($result)) {       

    echo '<b>Log Number:</b> '.$row['log_number'].'</td>';
    echo '<br/>';
    echo '<b>Name:</b> '.$row['name'].'</td>';
    echo '<br/>';
    echo '<b>City:</b> '.$row['city'].'</td>';
      
}
  mysqli_close($dbc);

?>


the problem I am having with my current code is that it does not display the results of the log number i searched for, but instead returns information for all the entries (two in my case) that have matching log numbers in both table1 and table2
when i search for log number 59, i get the following result

Log Number: 59
Name: Mark
City: Chicago

Log Number: 77
Transport Company: Doug
Customer Number: San Francisco



the result that I want is when i search for log number 59 ... i want it to only display that one record
Log Number: 59
Name: Mark
City: Chicago


hope someone can help
 

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

How do I join these queries and is it best practice?

$e_query = mysql_query("SELECT `employeeid`, `userid`, `companyid`, `function`, `timestamp` FROM `employees` WHERE `companyid` = '${c_row['companyid']}'");

$e_row = mysql_fetch_assoc($e_query);
         
$u_query = mysql_query("SELECT `id`, `firstname`, `lastname`, `username`, `accounttype` FROM `users` WHERE `id` = '${e_row['userid']}'");

$u_row = mysql_fetch_assoc($u_query);
         
$eQ = "SELECT `capitalrequested` FROM `eQuestions` WHERE `userid` = '${u_row['id']}'";
         
$eQRes = mysql_query($eQ);
         
$reQ = mysql_fetch_assoc($eQRes);
         
$iQ = "SELECT `capitalavailable` FROM `iQuestions` WHERE `userid` = '${u_row['id']}'";
         
$iQRes = mysql_query($iQ);
         
$riQ = mysql_fetch_assoc($iQRes);

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.

My query is 38 lines long so here is the simple version. Note: frequency is a number input by user from 0-720, 0 is the feature off.

$result = mysql_query("SELECT * FROM main_table
LEFT OUTER JOIN secondary_table ON main_table.cid=secondary_table.cid
WHERE
((frequency = 0) OR (fc_timestamp IS NULL) OR ('$current_timestamp'-frequency*3600 > fc_timestamp))
");

The data looks like this: (main_table cid is a unique id and in secondary there is a auto increment id column)

main_table
---------------
| cid* | a | b |
| 1  |  0 | 0 |
| 2  |  1 | 0 |
| 3  |  1 | 1 |
| 4  |  0 | 0 |
---------------

secondary_table
---------------
| cid | user_id | fc_timestamp |
| 1  |    5     |  1420417976  |
| 1  |    7     |  1420417999 |
| 2  |    9     |  1420417977 |
| 2  |    9     |  1420418976 |
| 2  |    111   |  1420419976 |
| 2  |    134   |  1420427976 |
| 3  |    111   |  1420417986 |
| 4  |    1001  |  1420417876 |
-------------------------------

What is happening is the query is pulling each line from the secondary table so I get multiple cid's as the result. I only want the row in the joined secondary table that is the maximum value of fc_timestamp.

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'];
}



?>

"                                                                                        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

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.