PHP - Looping From Join Query

Hello,

The query below works well. It pulls information from 3 MySQL tables: login, submission, and comment.

It creates a value called totalScore2 based on calculation of values pulled from these three tables.

The MySQL tables "comment" and "submission" both have the following fields:

Code: [Select]
loginid submissionid
In the table "submission," each "submissionid" has only one entry/row, and thus only one "loginid" associated with it.

In the table "comment," the field "submissionid" could have several entries/rows, and could be associated with multiple "loginid"s.

Each time one of the "submissionid"s in "comment" is associated with the same "loginid" that it has in the table "submission," I would like to add this as a factor to the equation below. I would like to multiple instances like this times (-10).

How could I do this?

Thanks in advance,

John

$sqlStr2 = "SELECT 
    l.loginid, 
    l.username, 
    l.created,
    DATEDIFF(NOW(), l.created) + COALESCE(s.total, 0) * 5 + COALESCE(scs.total, 0) * 10 + COALESCE(c.total, 0) AS totalScore2
FROM login l    
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM submission 
    GROUP BY loginid
) s ON l.loginid = s.loginid
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM comment 
    GROUP BY loginid
) c ON l.loginid = c.loginid
LEFT JOIN (
    SELECT S2.loginid, COUNT(1) AS total 
    FROM submission S2
    INNER JOIN comment C2
    ON C2.submissionid = S2.submissionid
    GROUP BY S2.loginid
) scs ON scs.loginid = l.loginid
GROUP BY l.loginid
ORDER BY totalScore2 DESC 
LIMIT 25";

Hey guys. Just need some help on how I can add  a query on row 2 if let's say a field is populated (Ex. spouse != null).
Here is the code but having a hard time trying to put that 2nd row while it's looping. Any help would be appreciated. Thanks!

        $data = '';
        while ( $row = mysql_fetch_row($export))
        {
                $line = '';
                foreach ( $row as $value )
                {
                        if ( ( !isset($value) ) OR ( $value == "" ) )
                        {
                                $value = "\t";
                        }
                        else
                        {
                                $value = str_replace('"', '""', $value);
                        $value = '="' . $value . '"' . "\t";
//        ^
//        Added an equal sign
                        }
                        $line .= $value;
                }
                $data .= trim($line)."\n";
        }
        $data = str_replace("\r", "", $data);

        // return message if query returns no data
        if ( $data == "" )
        {
                $data = "\n(0) Records Found!\n";
        }

        // setup headers with no caching
        header("Content-type: application/octet-stream");
        header("Content-Disposition: attachment; filename=data.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        print "$header\n$data";
} else {

Apologies if this should be in the mysql forum but hopefully this is the correct one.

I am trying to extract the sum of a column from my database for each financial year, at the moment I am doing each year as a separate recordset but I am certain there must be a more automated way!

The financial year is 1st April to 31st March each year and I need to create a variable which is a sum of each year, you'll see in my code below what I mean, any help gratefully appreciated as I am going to end up with decades of info in the db and am keen to get the code right now!

The relevant recordsets are 2,5,7.  db is readingID, date, reading

The code and page does work fine, it's just long winded!

Code: [Select]
<?php
mysql_select_db($database_wind, $wind);
$query_Recordset1 = "SELECT readingID, date_format(date,'%d/%m/%Y') as date, reading FROM solar ORDER BY readingID DESC LIMIT 5";
$Recordset1 = mysql_query($query_Recordset1, $wind) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_select_db($database_wind, $wind);
$query_Recordset2 = "SELECT SUM(reading) as year1total FROM `solar` WHERE date between '2010-04-01' and '2011-03-31'";
$Recordset2 = mysql_query($query_Recordset2, $wind) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

mysql_select_db($database_wind, $wind);
$query_Recordset3 = "SELECT * FROM solar ORDER BY readingID DESC Limit 29";
$Recordset3 = mysql_query($query_Recordset3) or die(mysql_error());

mysql_select_db($database_wind, $wind);
$query_Recordset4 = "SELECT readingID, YEAR(date) as yeardate, MONTHNAME(date) as monthdate, SUM(reading) as sumreading FROM `solar` Group by yeardate, monthdate Order by readingID ASC";
$Recordset4 = mysql_query($query_Recordset4) or die(mysql_error());

mysql_select_db($database_wind, $wind);
$query_Recordset5 = "SELECT SUM(reading) as year2total FROM `solar` WHERE date between '2011-04-01' and '2012-03-31'";
$Recordset5 = mysql_query($query_Recordset5, $wind) or die(mysql_error());
$row_Recordset5 = mysql_fetch_assoc($Recordset5);
$totalRows_Recordset5 = mysql_num_rows($Recordset5);

mysql_select_db($database_wind, $wind);
$query_Recordset6 = "SELECT datediff( Max(date), Min(date)) as DateDiff, Sum(reading) as LatestReading, date_format(MAX(date),'%d/%m/%Y') as LatestDate FROM solar";
$Recordset6 = mysql_query($query_Recordset6, $wind) or die(mysql_error());
$row_Recordset6 = mysql_fetch_assoc($Recordset6);
$totalRows_Recordset6 = mysql_num_rows($Recordset6);

mysql_select_db($database_wind, $wind);
$query_Recordset7 = "SELECT SUM(reading) as year3total FROM `solar` WHERE date between '2012-04-01' and '2013-03-31'";
$Recordset7 = mysql_query($query_Recordset7, $wind) or die(mysql_error());
$row_Recordset7 = mysql_fetch_assoc($Recordset7);
$totalRows_Recordset7 = mysql_num_rows($Recordset7);

$average = (int)(($row_Recordset6['LatestReading'])/($row_Recordset6['DateDiff']));
 
$income1 = $row_Recordset2['year1total']*0.428;
$income2 = $income1 + $row_Recordset5['year2total']*0.464;
$income = $income2 + $row_Recordset7['year3total']*0.464;

$saving = $row_Recordset2['year1total']*0.0675;
$saving = $saving + $row_Recordset5['year2total']*0.0675;
$saving = $saving + $row_Recordset7['year3total']*0.0675;

?>

I was running this query and feel this is slowing down the server:

Code: [Select]
$agent_query=$this->db->query("
                       SELECT u.id,u.name,u.team_id,l.agent_id from users AS u,leads AS l WHERE u.id=l.agent_id AND u.team_id
                        IS NOT   NULL AND u.is_active='1'
ORDER BY u.name ASC
");
Is there a better way to write the above code.

I am using two seperate queries to calculate a streak, but the queries must be grouped together to find the actual streak?

Query 1:

SELECT COUNT(matchID) as streak, clan1 FROM webs_cup_matches WHERE ladID='17' AND clan1='2630' AND score1 > score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan1 ORDER BY streak DESC LIMIT 1
Query 2:
SELECT COUNT(matchID) as streak, clan2 FROM webs_cup_matches WHERE ladID='17' AND clan2='2630' AND score1 < score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan2 ORDER BY streak DESC LIMIT 1

is it possible someone can join these queries together?

It's been a while since I sat down to build some pages and teach myself php. So now that I've started back up, I'm at a loss for what I've done. I deleted a file, and have to rebuild from an old broken version:

I have a form that submits a query to the database, but the results pages is giving me this error:

Code: [Select]
Oops, my query failed. The query is:
SELECT COUNT 'descriptors'.* ,'plantae'.* FROM 'descriptors' LEFT JOIN 'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name') WHERE 'leaf_shape' LIKE '%auriculate%' AND 'leaf_venation' LIKE '%%' AND 'leaf_margin' LIKE '%%'
The error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.* ,'plantae'.* FROM ' at line 2
But I'm not seeing what the syntax error is.

Here's the code:
Code: [Select]
<?php 
require ('connection.php');

$display = 2;
// it's intentionally only 2 for the moment

if (isset($_GET['np'])) {
   $num_pages = $_GET['np'];
} else { 
$data = "SELECT COUNT
                        'descriptors'.*
                        ,'plantae'.*
                    FROM
                        'descriptors'
                    LEFT JOIN
                        'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name')
                    WHERE
                        'leaf_shape' LIKE '%$s1%'
                        AND 'leaf_venation' LIKE '%$s3%'
                        AND 'leaf_margin' LIKE '%$s4%'";
$result = mysql_query ($data);

if (!$result) {
    die("Oops, my query failed.  The query is: <br>$data<br>The error is:<br>".mysql_error());
}

$row = mysql_fetch_array($result, MYSQL_NUM);

$num_records = $row[0];

if ($num_records > $display) {
   $num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}
}
if (isset($_GET['s'])) {
   $start = $_GET['s'];
} else {
    $start = 0;
}

if(isset($_POST[submitted])) {
// Now collect all info into $item variable
$shape = $_POST['s1'];
$color = $_POST['s2'];
$vein = $_POST['s3'];
$margin = $_POST['s4'];




// This will take all info from database where row tutorial is $item and collects it into $data variable 

$data = mysql_query("SELECT
                        'descriptors'.*
                        ,'plantae'.*
                    FROM
                        'descriptors'
                    LEFT JOIN
                        'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name')
                    WHERE
                        'leaf_shape` LIKE '%$s1%'
                        AND 'leaf_venation' LIKE '%$s3%'
                        AND 'leaf_margin' LIKE '%$s4%'
                    ORDER BY 'plantae'.'scientific_name` ASC LIMIT $start, $display");



//chs added this in... 
echo '<table align="center" cellspacing="0" cellpading-"5">
<tr>
<td align="left"><b></b></td>
<td align="left"><b></b></td>
<td align="left"><b>Leaf margin</b></td>
<td align="left"><b>Leaf venation</b></td>
</tr>
';
//end something chs added in 

// This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field.

while($row = mysql_fetch_array($data)){
echo '<tr>
<td align="left"> <a href="link.php">View plant</a> &nbsp; &nbsp;</td>
<td align="left"> <a href="link.php">unknown link</a>&nbsp; &nbsp;</td>
<td align="left">' . $row['scientific_name'] . '</td>
<td align="left">' . $row['common_name'] . '</td>
<td align="left">' . $row['leaf_shape'] . '</td>
</tr>';
}
echo '</table>';
// row 95
}
if ($num_pages > 1) {
echo '<br /><p>';
$current_page = ($start/$display) + 1;
// row 100
if ($current_page != 1) {
echo '<a href="leafsearch2a.php?s=' . ($start - $display) . '&np=;' . $num_pages . '">Previous</a> ';
}

for ($i = 1; $i <= $num_pages; $i++) {
if($i != $current_page) {
echo '<a href="leafsearch2a.php?s=' . (($display * ($i - 1))) . '$np=' . $num_pages . '">' . $i . '</a>';
} else {
echo $i . ' ';
}
}


if ($current_page != $num_pages) {
 echo '<a href="leafsearch2a.php?s=' . ($start + $display) . '$np=' . $num_pages . '"> Next</a>';
}
} 
//added curly
?>

Is there a cleaner way to do this?

$query = "SELECT branches.Language FROM eua_users, branches WHERE eua_users.AssignedBranch = branches.country" ;
include("dbconnectlocal.php") ;
$result = mysql_query($query) ;

$row = mysql_fetch_object($result) ;

$usrlang = $row->Language ;

$query = "SELECT UserName, Email FROM eua_users WHERE UserName = '$user'" ;
include("dbconnectlocal.php") ;
$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_object($result) ;

$branchmail = $row->Email ;

$query = "SELECT $usrlang FROM autoreplies WHERE ReplyID = '0'" ;
include("dbconnectlocal.php") ;
$result = mysql_query($query) or die(mysql_error()) ;

$row = mysql_fetch_object($result) ;

$message = $row->$usrlang ;

$query = "SELECT $usrlang FROM autoreplies WHERE ReplyID = '1'" ;
include("dbconnectlocal.php") ;
$result = mysql_query($query) or die(mysql_error()) ;

$row = mysql_fetch_object($result) ;

$url = $row->$usrlang ;

$query = "SELECT $usrlang FROM autoreplies WHERE ReplyID = '2'" ;
include("dbconnectlocal.php") ;
$result = mysql_query($query) or die(mysql_error()) ;

$row = mysql_fetch_object($result) ;

$subject = $row->$usrlang ;

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

hi,

This topic has been moved to MySQL Help.

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

Im currently working on a new feature called 'Request Player'. Basically what this does is allows managers to request players within their club for a upcoming fixture. There a multiple teams within a club (Senior As, Senior Bs, Colts U21s). Each team is controlled by a manager. And then they can add players as they need.

Anyway, I'm having problems with the sql JOIN.

I have currently got this:

$check = "SELECT u.* FROM users AS u LEFT JOIN requests AS r ON (u.id = r.player_id) WHERE (u.id = r.player_id)";


I have got two tables.

users - all the managers and players.
requests - all the requests the managers make to other teams within their club.

In the request table i have got this:

id
player_id - the players that has been requested.
fixture_id - the fixture the manager wants to have that player for.
accepted - if he has been accepted or not. This comes later on.

At the moment i have got it working so that it inserts this data, although the code is working but i want to have a mysql query that checks if a manager has any incoming requests from other teams within the club. BUT only request that has been made to his team....

Could someone please help me out?

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!

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.