PHP - Table Join Query

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

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

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

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!

SELECT R.RegionName, A.City 
FROM 
`activepropertylist` A,
`ParentRegionList` R,
`RegionEANHotelIDMapping` RM
WHERE MATCH(City, RegionName) AGAINST ('hervey bay' IN BOOLEAN MODE)
and A.EANHotelID = RM.EANHotelID
and RM.RegionID = R.RegionID
and R.RegionType = 'Neighborhood'

schema

http://developer.ean.com/database-catalogs/relational/geography-data/

book a suit search box example

http://www.bookasuite.com/

I am trying to join 3 tables together to get the correct information from expedia tables for my auto drop down box   the schema is here, located in the link and I am looking auto search like book a suite do(link also provided)   problem is I can manage to get the word hervey bay out of the property region list table but the incorrect city comes back from the activeproperylist table(it seems to be showing something from the other table for the city)     here is my query        

This topic has been moved to MySQL Help.

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

I'm outputting my DB to a table. I want to include a JOIN so the table shows name, email, and company (which is in a different table). Not sure where to put the join.
2 other things I'd like to do:
Not all users have email and I want any NULL to output as N/A instead of NULL.
Would like the rows to alternate background color between white and blue for each row.
Any hints appreciated.



$query= "select * from Managers";
$result=mysql_query($query);
echo mysql_error();

echo '<table align="center" cellspacing="1" cellpadding="2">
<tr>
<td align="left"><b>Name</b></td>
<td align="left"><b>Email</b></td>
<td align="left"><b>Company</b></td>
</tr>
';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<tr bgcolor="' . $bg . '">
<td align="left">' . $row['name'] . '</td>
<td align="left">' . $row['email'] . '</td>
<td align="left">' . $row['company.name'] . '</td> //Need JOIN here
</tr>
';
}

Dear All,

As I would like to create json as below format 

{
    "data": [
        {
            "title": "After getting a COVID-19 Vaccine",
            "content": [
                {
                    "list": "You may have some side effects, which are normal signs that your body is building protection."
                },
                {
                    "list": "Following symptoms can take place, and it usually resolves within 2 to 3 days."
                },
                {
                    "list": "Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache"
                }
            ]
        },
        {
            "title": "After getting a COVID-19 Vaccine",
            "content": [
                {
                    "list": "You may have some side effects, which are normal signs that your body is building protection."
                },
                {
                    "list": "Following symptoms can take place, and it usually resolves within 2 to 3 days."
                },
                {
                    "list": "Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache"
                }
            ]
        }
    ]
}

The information is from  mysql select command:

SELECT (tb_helpinfo.title_eng) as title,(tb_helpdetail.content_eng) as content FROM tb_helpinfo INNER JOIN tb_helpdetail ON tb_helpinfo.id = tb_helpdetail.helpinfo_id

 

How could I make json like that from mysql information?

I have two mysql tables, one is called "posts", the other is called "removed_posts", I have joined them together and can get some of the information from the tables, but I want to be able to get all of the information.

This is my query, and the variables I need the information to go to:

Code: [Select]
$quer = mysql_query("SELECT posts.id, removed_posts.post_id FROM posts, removed_posts WHERE posts.id=removed_posts.post_id AND removed_posts.user_id='".$session."'");
while($get_post = mysql_fetch_array($quer))
{
$session = $_COOKIE['id'];
$to_id = $get_post['to_id'];
$from_id = $get_post['from_id'];
$post = linkify(nl2br(htmlentities($get_post['post'])));
$date = time_stamp($get_post['date']);
$id = $get_post['id'];
What that's doing is selecting the id from 'posts' table, and selecting the post_id from the 'removed_posts' table (The query checks to make sure posts.id is equal to removed_posts.post_id), it does that just fine, since I defined what information I wanted it to get in the query, however, now I need to get the other information which will come from the 'posts' table.

$get_post['to_id'],
$get_post['from_id'],
$get_post['post'], and
$get_post['date']

I hope that's not toooooo confusing, any help would be appreciated

I'm trying to conditionally add a table to a select query using the join, but I always get an "no such table"  error.  I'm not at all sure if the syntax is correct, so I decided to ask.    I'm using the sqlite C API interface and my tables and query were constructed using sprintf, so please ignore any %d, %s that may appear

CREATE TABLE IF NOT EXISTS tbl_master
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     volume TEXT(16) UNIQUE NOT NULL DEFAULT '',
     note   TEXT(%d) NOT NULL DEFAULT '',
     items INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_file
(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      volume_key TEXT(16) NOT NULL DEFAULT '',
      name TEXT(%d),
      size TEXT(20),
      type TEXT(4),
      path TEXT(%d),
      file_id INTEGER
);
 
CREATE TABLE IF NOT EXISTS tbl_hash
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     hash TEXT(33) NOT NULL DEFAULT '',
     volume_key TEXT(16) NOT NULL DEFAULT '',
     file_key INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_media
(
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       runtime TEXT(10) NOT NULL DEFAULT '',
       frame TEXT(12) NOT NULL DEFAULT '',
       type_key TEXT(4) NOT NULL DEFAULT '',
       hash_key TEXT(33) NOT NULL DEFAULT '',
       volume_key TEXT(16) NOT NULL DEFAULT '',
       file_key INTEGER NOT NULL DEFAULT 0
);

I milled over a few JOIN tutorials, but I'm still unclear on the exact usage of JOIN; I'm trying to add the media table if the file type is a vid, snd, or pix, but I need file information regardless.  I've tried various flavors of the following query, but each time I get the table doesn't exist error.  Selects, deletes, updates, inserts work on all tables, so I'm guessing my syntax is wrong with the JOIN.

SELECT tbl_file.*, tbl_hash.hash
  FROM tbl_file AS f, tbl_hash AS h
  LEFT OUTER JOIN tbl_media AS m
  ON  ((f.type='VID' OR f.type='SND' OR f.type='PIX')
  AND m.file_key=f.file_id)
  WHERE ((f.volume_key=tbl_master.volume
  AND (h.volume_key=tbl_master.volume AND h.file_key=f.file_id)))
  ORDER BY f.path ASC;

Any ideas on how to pull off what I'm trying to do would be greatly appreciated.  Thank you for your time.      

This topic has been moved to MySQL Help.

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