PHP - Moved: Sql Insert With Inner Joins

I'm a newbie in PHP/MySQL.  In MS-Access, I can set up a permanent relationship between tables.  Does this not exist in MySQL?  Do all the relationships need to be established/re-established with each query? Or is there a way to establish a permanent relationship?

Hi guys, I never learnt joins, and this is what my code looks like as a result, Can someone just show me how to convert the following from 2 queries to one? It is a blog post and the second query is getting an author I have shortened it for convenience.    Thanks in advance.

$getBlogs = mysql_query("SELECT * FROM blogs WHERE blogStatus='1'", $retreat);
		while($row = mysql_fetch_array($getBlogs)){
			$blogID = $row['blogID'];
			$authurID = $row['authurID'];
		}

		$getAuthor = mysql_query("SELECT * FROM blog_authurs WHERE authurID='$blogAuthur' LIMIT 1", $retreat);
			while($row = mysql_fetch_array($getAuthor)){
				$authurName = $row['authurName'];
			}

This topic has been moved to MySQL Help.

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

This topic has been moved to Ajax Help.

http://www.phpfreaks.com/forums/index.php?topic=350408.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?

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

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?

I have this code which should get all the results from forum_forums and list them in their respective parents using forum_forums.parent_id. It does work sort of but it repeats one of them and doesnt include all of them.

Can anyone see where i am going wrong?

$parent_info_query = $db->query("SELECT 
                    parent_id,
                    parent_name
                    FROM ".DB_PREFIX."parents")
                    or die(mysql_error());

while ($parent_info = mysql_fetch_object($parent_info_query)) { 
 
// Add parent_id into variable for later query
 
 $parent_id = $parent_info->parent_id;

echo '<table class="forum_table" onclick="expandCollapseTable(this)">
<tr id="tr1">
<th class="forum_left_corner"></th>
<th class="forum_parent_name">'.$parent_info->parent_name.'</th>
<th class="empty"></th>
<th class="empty"></th>
<th class="forum_last_post_header">'.LAST_POST.'</th>
</tr>';

// Get Forum information from DB to show all forums
// including who the last post was posted by

$forum_info_query = $db->query("SELECT 

                    ".DB_PREFIX."forums.forum_id,
                    ".DB_PREFIX."forums.forum_name,
                    ".DB_PREFIX."forums.forum_description,
                    ".DB_PREFIX."forums.forum_topics,
                    ".DB_PREFIX."forums.forum_posts,
                    ".DB_PREFIX."forums.forum_last_poster,
                    ".DB_PREFIX."forums.forum_last_post_time,
                    ".DB_PREFIX."forums.forum_last_post,
                    
                    ".DB_PREFIX."members.user_id,
                    ".DB_PREFIX."members.user_username,
                    ".DB_PREFIX."members.user_group,
                    
                    ".DB_PREFIX."topics.topic_id,
                    ".DB_PREFIX."topics.topic_name
                    
                    FROM ".DB_PREFIX."forums
                    
                    JOIN ".DB_PREFIX."members
                    
                    ON ".DB_PREFIX."forums.forum_last_poster
                    = ".DB_PREFIX."members.user_id
                    
                    JOIN ".DB_PREFIX."topics
                    
                    ON ".DB_PREFIX."forums.forum_id
                    = ".DB_PREFIX."topics.forum_id
                    
                    WHERE ".DB_PREFIX."forums.parent_id 
                    = $parent_id") 
                    
                    or trigger_error("SQL", E_USER_ERROR);
                    
while ($forum_info = mysql_fetch_object($forum_info_query)) {

im trying to improve my code by using table joins but when i use the following code it just returns everything in the database rather than the results that equal $forum_id which is the get value 2. There should only be 2 results.

 $query = $db->query("SELECT 
                    ".DB_PREFIX."topics.topic_id,
                    ".DB_PREFIX."topics.topic_name,
                    ".DB_PREFIX."topics.topic_poster,
                    ".DB_PREFIX."topics.topic_time_posted,
                    ".DB_PREFIX."topics.topic_views,
                    ".DB_PREFIX."topics.topic_replies,
                    ".DB_PREFIX."topics.topic_last_poster,
                    ".DB_PREFIX."topics.topic_last_post_time,
                    ".DB_PREFIX."topics.topic_locked,
                    ".DB_PREFIX."topics.topic_sticky,
                    
                    ".DB_PREFIX."parents.parent_id,
                    ".DB_PREFIX."parents.parent_name,
                    
                    ".DB_PREFIX."forums.forum_name,
                    
                    ".DB_PREFIX."members.user_username,
                    ".DB_PREFIX."members.user_group
                    
                    FROM ".DB_PREFIX."topics
                    
                     JOIN ".DB_PREFIX."members
                     JOIN ".DB_PREFIX."parents
                     JOIN ".DB_PREFIX."forums
                    
                    ON ".DB_PREFIX."topics.topic_poster 
                    = ".DB_PREFIX."members.user_username
                    
                    WHERE ".DB_PREFIX."forums.forum_id
                    = ".$forum_id."
                    
                    ORDER BY ".DB_PREFIX."topics.topic_time_posted $max")
                    or trigger_error("SQL", E_USER_ERROR);

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?

This topic has been moved to JavaScript Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to Third Party PHP Scripts.

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

This topic has been moved to JavaScript Help.

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

This topic has been moved to MySQL Help.

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