PHP - Table Joins

Check out my code below and you will see what I am trying to do.

I cannot figure out what is going wrong.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mskordus/public_html/index.php on line 25

Code: [Select]
<?php
$result = mysql_query("SELECT * FROM students,teachers,specialEd,course,sched,notes WHERE
   students.studentId=notes.studentId AND
   students.studentId=sched.studentId AND
   students.teacherId=teachers.teacherId AND
   students.specialId=specialEd.specialId AND
   students.courseId=course.courseId AND
   ");
echo "<table>";
while ($row = mysql_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['students.fName'] . "</td>";
echo "<td>" . $row['teachers.fName'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>

This topic has been moved to MySQL Help.

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

Hi,

I'm trying to a query to check whether a record has a reference in two other tables, I have the following:


Explain SELECT COUNT(*) AS new FROM entity_details
  LEFT JOIN entity_turned_prospect
ON entity_details.id = entity_turned_prospect.entityRef
LEFT JOIN entity_turned_customer
ON entity_details.id = entity_turned_customer.entityRef
 WHERE entity_details.ownerRef = 41
    AND entity_details.typeRef = 4
 AND entity_details.dateCreated = MONTH(NOW())
    AND entity_turned_prospect.id = NULL
 AND entity_turned_customer.id = NULL


But I get 0 results returned when I know for a fact there should be some.

If I change the count to just select * I then get an error:

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 'AS new FROM entity_details, LEFT JOIN entity_turned_prospect ON' at line 1

Could someone show me how to fix this?

I've got a region-type select form where a user chooses his/her continent, and depending on the selection, a second select form is filled with country values relating to that continent. From there, depending on the country selection, a third select form is dynamically filled with regions relating to that country and continent. See the code attached:

<form action="" method="POST">
    <select id="continent" name="continent" style="min-width: 170px;">
        <option value="">Select Continent</option>
        <?php
        $q = "SELECT * FROM table_areaContinent";
        $r = mysql_query($q) or die(mysql_error());
        
        while($row = mysql_fetch_array($r)){
            $continent_id = $row['cont_id'];
            $continent = $row['continent'];
            
            print '<option value="' . $continent_id . '">' . $continent . '</option>' . "\n";
        }
        ?>
    </select>
    
    <br /><br />
    
    <select id="country" name="country" style="min-width: 170px;">
        <option value="">Select Country</option>
        <?php
        $q = "SELECT * FROM table_areaCountry JOIN table_areaContinent USING (cont_id)";
        $r = mysql_query($q) or die(mysql_error());
        
        while($row = mysql_fetch_array($r)){
            $country_id = $row['country_id'];
            $country = $row['country'];
            $continent_id = $row['cont_id'];
            
            print '<option value="' . $country_id . '" class="' . $continent_id . '">' . $country . '</option>' . "\n";
        }
        ?>
    </select>
    
    <br /><br />

    <select id="region" name="region" style="min-width: 170px;">
        <option value="">Select Region</option>
        <?php
        $q = "SELECT * FROM table_areaRegion JOIN table_areaCountry USING (country_id)";
        $r = mysql_query($q) or die(mysql_error());
        
        while($row = mysql_fetch_array($r)){
            $region_id = $row['region_id'];
            $region = $row['region'];
            $country_id = $row['country_id'];
            
            print '<option value="' . $region_id . '" class="' . $country_id . '">' . $region . '</option>' . "\n";
        }
        ?>    
    </select>
    
    <br /><br />
    
    <input type="submit" value="Submit" name="submit" class="submit" />
</form>

Now, I simply just want to echo out the Continent, Country and Region that the user selected after the form is submitted. I currently have this :

<?php
if(!empty($_POST['submit'])){
    $continent = $_POST['continent'];
    $country = $_POST['country'];
    $region = $_POST['region'];
    
    print '
    <b>Continent: ' . $continent . '<br />
    <b>Country: ' . $country . '<br />
    <b>Region: ' . $region . '<br />
    ';
}
?>

Which echoes out the ID numbers for each variable. What would be the best direction to take when pulling data from multiple tables to echo out the actual Names that the IDs are assigned to?

I have created a file with this basic structure in the referals table:
refREFER = the referer, person who sent the referal link out
refREFED = the refered person, the one who signed up with the link
refREFERIP = the referer's IP
refREFEDIP = the refered person's IP

I'm trying to create a code that will list any user that has signed up on the same IP with a referal link. Everything works except the Refered column, which I can't seem to figure out how to do. Basically I have made the refREFER = the userid in the users table, so it can be output as a username. However I want to do the same with the refREFED but don't know how. Here's my code so far:


<?php
include "globals.php";
if($ir['user_level'] != 2 && $ir['user_level'] != 3) //User level check
{
    die("You can't access this page");
}

$q=mysql_query("SELECT u.userid, u.username, u.laston, r.* 
FROM users u 
LEFT JOIN referals r ON r.refREFER=u.userid 
WHERE r.refREFERIP=r.refREFEDIP 
ORDER BY userid ASC",$c); //Selecting everything that's needed

print "<center><br /><b><font color=white><h1>Referral Multis</h1></b><br />

<table width=75% border=1>
<tr style='background:black'>
<th><font color=grey>Referer</font></th>
<th><font color=grey>Refered</font></th>
<th><font color=grey>IP</font></th> 
<th><font color=grey>Time</font></th>
</tr>";
while(($r=mysql_fetch_array($q) or die(mysql_error()))) //Making all of the select stuff into $r
{
$reftime = date('F j, Y g:i:s a', $r['refTIME']);
if($r['laston'] >= time()-60*60) { $on="<font color=green><b>Online</b></font>"; } else { $on="<font color=red><b>Offline</b></font>"; }
print "
<tr> 
<td><a href='viewuser.php?u={$r['userid']}'><font color=brown><b>{$r['username']}</b></font></a> [{$r['refREFER']}]</td>
<td> {$r['username']} - {$r['refREFED']}</td>
<td>{$r['refREFERIP']}</td>
<td>$reftime</td>
</tr>";
}
print "</table>";

$h->endpage();

?>


At the moment under the Refered column it shows the wrong username, but the correct ID. Any ideas?

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

Given the below 3 database tables I am trying to construct a SQL query that will give me the following result:

customer_favourites.cust_id
customer_favourites.prod_id OR product.id
product.code
product.product_name
product.hidden
product_ section.section_id (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.catpage (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.relative_order (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)

I currently have....

SELECT customer_favourites.cust_id, customer_favourites.prod_id, product.code, product.product_name, product.hidden, product_section.section_id, product_section.relative_order, product_section.catpage  
FROM customer _favourites  
INNER JOIN product ON customer_favourites.prod_id = product.id  
INNER JOIN product_section ON product_section.product_code = product.code  
WHERE `cust_id` = '17' AND `hidden` = '0' GROUP BY `code`  
ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC LIMIT 0,30

This gives me what I want but only sometimes, at other times it randomly selects any row from the product_section table.

I was hoping that by having the row I want as the last row (most recent added) in the product_section table then it would select that row by default but it is not consistent.

Somehow, I need to be able to specify which row to return in the product_section table, it needs to be the row with the lowest section_id value or it should by the last row (most recent).

Pulling my hair out so any help is gratefully received.  
customer_favourites

id     cust_id   prod_id
70    4            469
product

id        code           product_name    hidden
469    ABC123     My Product        0
product_section

id                  section_id      catpage    product_code    relative_order     recommended
44105         19                   232            ABC123             260                    1
44106          3                     125            ABC123              87                      1
44107          2                     98              ABC123             128                     1
44108          1                    156             ABC123             58                        0

 

Hi All, I want to copy into a table values from another table that partially match a given value, case-insensitively. So far I do as follows but I wonder whether there is a quicker way.

$input_table=array('1'=>'toto','2'=>'tota','3'=>'hello','4'=>'TOTO','5'=>'toto');
$input_table_2 = array_map('strtolower', $input_table);
$value_to_look_for='Tot';
$value_to_look_for_2=strtolower($value_to_look_for);
$output_table=array();
foreach ($input_table_2 as $k=>$v)
  {
  if(false !== strpos($v, $value_to_look_for_2))
    {
    $output_table[]=$input_table[$k];
    }
}

One drawback is that $input_table_2 is 'foreached' whereas there might be no occurrences, which would lead to a loss of time/resources for big arrays.   Thanks.

Hello,

I need some help. Say that I have a list in my MySQL database that contains elements "A", "S", "C", "D" etc... Now, I want to generate an html table where these elements should be distributed in a random and unique way while leaving some entries of the table empty, see the picture below. But, I have no clue how to do this... Any hints?



Thanks in advance,
Vero

Hi All ,   I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:   Day_ID -- Dues ---  Last_Visit ---  Points. 1  --------- 900 -------- 1/12  --------   6      2  --------- 700 -------- 4/12  --------   7 3  --------- 600 -------- 7/12  --------   5 4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.    So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:   4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >=
(
SELECT MAX(last_visit) FROM clubvisit
WHERE points <>
(
SELECT points as lastpoints
FROM clubvisit
JOIN
(
SELECT MAX(last_visit) as last_visit
FROM clubvisit
) as latest USING (last_visit)
)
)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.   As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible.    The values that I require for each of the cv.point columns is  1. day_id of the previous / old day on which the cv.points value changed from the current day value, and  2. cv.points on that old/ previous day.    So for  example if the table is as below:   Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2. 1  --------- 900 -------- 1/12  -----------   9  ------------ 5    2  --------- 600 -------- 4/12  -----------   6  ------------ 6 3  --------- 400 -------- 7/12  -----------   4  ------------ 7 4  --------- 500 -------- 9/12  -----------   5  ------------ 8 5  --------- 600 -------- 10/12 ----------   6  ------------ 8 6  --------- 600 -------- 11/12 ----------   6  ------------ 8 7  --------- 600 -------- 13/12 ----------   6  ------------ 7 8  --------- 500 -------- 15/12 ----------   5  ------------ 7 9  --------- 500 -------- 19/12 ----------   5  ------------ 7   Then I need the following set of values : 1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1 2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3) 3. day_id3 -- ....   and so on for other points.    Thanks all !

hi...

i have a table ...

i add and remove data in the table...when i add new record , information add to center of the table ! whats problem?   i want add data in first of table.   please guide me.thanks

 

 

Hi I am very new to PHP & Mysql. I am trying to insert values into two tables at the same time. One table will insert a single row and the other table will insert multiple records based on user insertion. Everything is working well, but in my second table, 1st Table ID simply insert one time and rest of the values are inserting from 2nd table itself. Now I want to insert the first table's ID Field value (auto-incrementing) to a specific column in the second table (only all last inserted rows).   Ripon.   Below is my Code:

<?php



$con = mysql_connect("localhost","root","aaa");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("ccc", $con);


$PI_No = $_POST['PI_No'];
$PO_No = $_POST['PO_No'];


$qry = "INSERT INTO wm_order_entry (
Order_No,
PI_No,
PO_No) 


VALUES(
NULL,
  '$PI_No',
 '$PO_No')";
$result = @mysql_query($qry);




$val1=$_POST['Size'];
$val2=$_POST['Style'];
$val3=$_POST['Colour'];
$val4=$_POST['Season_Code'];
$val5=$_POST['Dept'];
$val6=$_POST['Sub_Item'];
$val7=$_POST['Item_Desc'];
$val8=$_POST['UPC'];
$val9=$_POST['Qty'];
$N = count($val1);
for($i=0; $i < $N; $i++)
{


$profile_query = "INSERT INTO order_entry(Size,
Style,
Colour,
Season_Code,
Dept,
Sub_Item,
Item_Desc,
UPC,
Qty,
Order_No
) VALUES( '$val1[$i]','$val2[$i]','$val3[$i]','$val4[$i]','$val5[$i]','$val6[$i]','$val7[$i]','$val8[$i]','$val9[$i]',LAST_INSERT_ID())";
$t_query=mysql_query($profile_query);
}
header("location: WMView.php");
mysql_close($con);
?>

Output is attached.

Hello everyone.

I need help with the following PHP APP. I am running on (Version PHP 7.2.10) I am trying to have a page table form on table.php pass the input variable of “5-Numbers” to another page called table_results.php

I want that variable string of “5-Numbers” to be compared against 4 arrays and output any duplicates found within each of those 4 lists. If nothing is found, I still want some visual output that reads “None found”.

 

Lets pretend I have the following example ..

On table.php, I typed inside my table form the 5-Numbers ..

INPUT:  2,15,37,13,28

On table_results.php, the 4 arrays to be compared against my input numbers “ 2,15,37,13,28” are ..

 

$array_A = array(2,6,8,11,14,18,24); 

$array_B = array(1,2,9,10,13,14,25,28,);  

$array_C = array(1,3,7,9,13,15,20,21,24); 

$array_D = array(4,5,12,22,23,27,28,29); 

 

So my output should read as follows below ..

OUTPUT: 

 

TABLE

COLUMN 1   COLUMN 2

ROW 1 Matches Found Results ..

ROW 2 GROUP A:            2 

ROW 3 GROUP B:             2,13,28

ROW 4 GROUP ?            13,15

ROW 5 GROUP ?            28

ROW 6 5#s Input:              2,15,37,13,28

 

Please let me know if anyone has any suggestions on how to go about it. 

Thanks.

Edited January 1, 2019 by Jayfromsandiego
Wanted to include image example

I know I'm doing it something right, but can someone tell me why only one table is showing up? Can you help me fix the issue?

Heres my code:


function showcoords()
{
echo"J3st3r's CoordVision";
$result=dbquery("SELECT alliance, region, coordx, coordy FROM ".DB_COORDFUSION."");
dbarray($result);
$fields_num = mysql_num_fields($result);

echo "<table border='1'>";
// printing table headers


    echo "<td>Alliance</td>";
    echo "<td>Region</td>";
    echo "<td>Coord</td>";


// printing table rows
while($row = mysql_fetch_array($result))
{
    

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row AS $Cell)
     echo "<tr>";     
     echo "<td>".$row['alliance']."</td>\n";
     echo "<td>".$row['region']."</td>\n";
     echo "<td>".$row['coordx'].",".$row['coordy']."</td>\n";
     echo "</tr>\n";
    
}
echo "</table>";
mysql_free_result($result);

}



I have 2 rows inserted into my coords table. Just frustrated and ignorant to php.