PHP - Join Or Union To Make Query Less Messy?

This topic has been moved to MySQL Help.

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

Hi guys,   I’m really hoping someone can help with this query. I'm sure it must use join somehow but i cant work out exactly how to do it.   I have two tables (‘booking_slots’ and ‘booking_reservation’). ‘booking_slots’ has ‘slot_date’ and ‘slot_id’ fields. ‘booking_reservation’ has a number of fields including ‘slot_id’ but not ‘slot_date’.   I want to run a query to delete all records between a certain date range in BOTH tables (say for example 01 Jan 2012 to 01 Jan 2013). To do this I want to find and delete all records using ‘slot_date’ in ‘booking_slots’ and use the corresponding ‘slot_id’ of the deleted records to delete the records with the same ‘slot_id’ in ‘booking_reservation’.   Any help with this would be very greatly appreciated.   Thanks

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";

I am having a warning which indicates there is a not valid mysql result, I think the problems lay down at the WHERE clause, but I am not sure.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /storeprueba/sidebar.php on line 21

Code: [Select]

$categoryurl = $_GET['categoryurl'];
$sql= mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6");
$productCount = mysql_num_rows($sql); // line 21
if ($productCount>0 )
     {
     while($row = mysql_fetch_array($sql))
          {
            $id= $row["id"];
$product_name= $row["product_name"];
$price = $row["price"];
$category = $row["category"];
$subcategory = $row["subcategory"];
$location = $row["location"];
$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));


thanks.

Hi, I have a fantasy football website, and on a user account page I want to display fixtures that are coming up that include teams that the current user has chosen. My test_teams table stores all the team names and their teamid. The test_selections table is where each users team selections are stored, it has two columns, userid and teamid. The test_fixtures table has two columns, hometeam and awayteam, these two cloumns hold the teamid of the teams that are playing.

The code below correctly displays the fixtures that contain any of the current users team selections. However, it is only displaying the teamid of the teams that are playing as they have not been matched to the test_teams table to get the team name. Does anybody now how I can do this? I believe it can be done using a left join but so far I just keep getting errors when i try to write the code. Any help would be very much appreciated.

Code: [Select]
<table width="380" border="0">
<?php
$query = "SELECT test_fixtures.competition, test_fixtures.date, test_fixtures.hometeam, test_fixtures.awayteam FROM test_fixtures, test_selections WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam)";
        $result = mysql_query($query) or die(mysql_error());
        
        while($row = mysql_fetch_assoc($result))
{
?>
<tr>
<td width="85" class="fixtures_date"><?php echo $row['date']; ?></td>
<td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td>
<td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td>
<td width="25" class="fixtures_center">v</td>
<td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td>
</tr>
<?php
}
?>
</table>

Hi.. I think I have bad code in three different similar query's in my code.

The first query is:  $sql="SELECT * FROM invoice as d INNER JOIN members as c ON d.buyer=c.usernum order by " . $orderBy . " " . $order;

$result = $con->query($sql);

Actually in this one I didn't even know I had a problem other than it was slow working until I put this error trap in:

if (!$check1_res) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}

The other two pass $id from the previous script they a

$sql=mysqli_query($con,"SELECT * FROM invoice_items as d inner JOIN items as c ON d.itemnum=c.itemnum where invnumber = '$id'");

$row = mysqli_fetch_array($sql);

and

$sql=mysqli_query($con,"SELECT * FROM invoice as d inner JOIN members as c ON d.buyer=c.usernum where invnumber = '$id'");

$row = mysqli_fetch_array($sql);

To be honest I didn't know I had a problem with the first two until The third would not return the correct data.  I only got partial or none of the invoice data I was expecting.  But I realized all three have problems when I used the error trap which come back with this:

Notice: Undefined variable: check1_res in C:\Apache24\htdocs\choo\tc_invoice.php on line 37
Error:
Notice: Undefined variable: check1_res in C:\Apache24\htdocs\choo\tc_invoice.php on line 83
Error:

The error I get is identical for the first query.

At first I thought it was the way that I was passing the variable, but $id is valid when echoed just below the query.

So I am hoping that this is going to be something I did wrong with the querys or possibly the fetch statement after the query is wrong???

Really appreciate any help you can give me.  If you need to see more of the code on either script please let me know.

Sorry, I originally posted part of this in MYSQL Help, but I was advised to post it here. I'm hoping someone can help me out.

I have 2 tables. "categories" and "subcats" (sub categories). I used a JOIN query for both..

Code: [Select]
SELECT categories.*, subcats.* FROM categories JOIN subcats on (categories.cat_id = subcats.cat_id) ORDER BY cat_name
Then I used a while loop to echo all the categories from the "categories" table and their respective sub-categories from the "subcats" table.

$sql = "SELECT categories.*, subcats.* FROM categories JOIN subcats on (categories.cat_id = subcats.cat_id) ORDER BY cat_name";
$cats_result = $connection->query($sql) or die(mysqli_error($connection)); 

    while ($row = $cats_result->fetch_assoc()) { 
$cat_id = $row['cat_id'];
$cat_name = $row['cat_name'];
$subcat_name = $row['subcat_name'];

       //should output all categories once and nest their subcategories
echo "<strong>".$cat_name."</strong>"; 
echo "<br />";
echo $subcat_name; 
echo "<br />";

}

No MYSQL errors but unfortunately I get this output..

Technology
Computers

Technology
Gadgets

Technology
Robots

Health
Fitness

Health
Diet

What sort of code can use in the loop to echo categories just once instead of multiple times for each topic?

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?

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.

This topic has been moved to MySQL Help.

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

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
?>

This topic has been moved to MySQL Help.

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

hi,

I think I'm nearly there, I just want to display a drop down list

Code: [Select]
<ul>
<li><form name="form3" method="post" action="">
              <select name="menu2" onChange="MM_jumpMenu('parent',this,0)" class="textbox">
                <option value="">Please choose a town!</option>
<?php while($row = mysql_fetch_array($result))
{
echo '<option value=\"pub_info.php?rsTown='.row['rsTown'].'\">';
echo ''.row['rsTown'].'&nbsp;('.$total_pages.')</option>';
}
            echo '</select>';
            echo '</form>';
?>
</li>
</ul>

my error lies on line 465 which is this bit: Code: [Select]
echo '<option value=\"pub_info.php?rsTown='.row['rsTown'].'\">';
echo ''.row['rsTown'].'&nbsp;('.$total_pages.')</option>';

but I think my code is just a mess?! what do you think?

This topic has been moved to MySQL Help.

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