PHP - Join Query Solution For Multiple Table Check.

Hi guys,
I need your help!
I have two tables "sp_users" and "sp_schools" in the same database.
Now I want to add column "time" from "sp_schools" to a phpfile which displays "sp_users".
The column "time" should be after column "update".

I made it upto here, but to get the second query and display
Please help. I am devastated...

Code: [Select]
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="sp_users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name ORDER BY user_id";
$result=mysql_query($sql);
?>
<style type="text/css">
<!--
.style2 {font-weight: bold}
.style3 {
font-family: Arial, Helvetica, sans-serif;
color: #000033;
}
.style8 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #003333; }
-->
</style>

<title>User overview</title><table width="486" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td width="427">
 
    <div align="left">
      <table width="486" border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td colspan="4"><div align="center" class="style1 style3"><strong>SchoolPorta Users </strong></div></td>
        </tr>
        <tr>
          <td width="26" align="center"><span class="style2">id</span></td>
          <td width="70" align="center"><span class="style2">Name</span></td>
          <td width="114" align="center"><span class="style2">Lastname</span></td>
          <td width="146" align="center"><span class="style2">Email</span></td>
          <td width="88" align="center"><span class="style2">Update</span></td>
        </tr>
        <?php
while($rows=mysql_fetch_array($result)){
?>
        <tr>
          <td><span class="style8"><? echo $rows['user_id']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_first_name']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_surname']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_login']; ?></span></td>
          <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style8">update</a></td>
        </tr>
        <?php
}
?>
      </table>
    </div></td>
</tr>
</table>
<div align="center">
  <?php
mysql_close();
?>
</div>

This topic has been moved to MySQL Help.

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

I use a script for sharing links. Everything works ok, but checking for valid images can take a long time.
First through CURL method I parse certain tags (for description and title) then I use the following script to find images from the submited link:

Code: [Select]
$dom = new domDocument;
  @$dom->loadHTML('<?xml encoding="UTF-8">' . substr($content,0,35000));
  $dom->preserveWhiteSpace = false;
  $images = $dom->getElementsByTagName('img');

It sometimes happens that the images have not not a good path(depending on whether the link is permanent or not) and then use the getimagesize function that works well. But it can be very slow ...sometimes it takes 15-30 seconds.
Without function getimagesize process takes a few seconds.
Is there a better (faster) method to check whether the image is valid or not?

I want to have a search product feature, but I would like members to be able to search multiple fields in one go i.e.  product_code, Product_name in one MySQL query.

The thing is, members have to be logged on, so the query must also only show results relating to that specific member, via the session[member_ID], my current query for listing products for that specific member is :

Code: [Select]
$sql = "SELECT productId, productCode, image, name, price, stock_level FROM product_inventory WHERE memberr_ID = '" . $_SESSION['SESS_mem_ID'] . ";
How would I change the above into a search query to search for productcode, productname and still only show results beloging to this member using the session data ? 

all help appreciated

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

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.

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.

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?

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

I have code that queries two tables for example... Code: [Select]
$checkfordue = mysql_query("SELECT stuID FROM ".$conf['tbl']['homework']." , ".$conf['tbl']['homeworktable']." WHERE ".$conf['tbl']['homework'].".TID = '$newID' && ".$conf['tbl']['homeworktable'].".TID = '$newID' && ".$conf['tbl']['homeworktable'].".ID = '$ID' &&
".$conf['tbl']['homework'].".turnin = '2' && ".$conf['tbl']['homework'].".homeworkID = ".$conf['tbl']['homeworktable'].".ID ORDER BY stufirstname, stulastname");
WHILE($checkdue = mysql_fetch_array($checkfordue)) {etcAnd the above code has been working fine.  The question really is about using JOIN instead of what I did.  And if you could please explain join left as well.  What are the benefits of using join rather then writing the query like I did.

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 ;

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=313679.0