PHP - I Need Help With Mysql Joins

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

This topic has been moved to MySQL Help.

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

create table mimi (mimiId int(11) not null, mimiBody varchar(255) );
<?php
//connecting to database
include_once ('conn.php');
$sql ="SELECT mimiId, mimiBody FROM mimi";

$result = mysqli_query($conn, $sql );

$mimi = mysqli_fetch_assoc($result);

$mimiId ='<span>No: '.$mimi['mimiId'].'</span>';

$mimiBody ='<p class="leading text-justify">'.$mimi['mimiBody'].'</p>';
?>
//what is next?

i want to download pdf or text document after clicking button or link how to do that

Hello everyone,

Sorry if this has been answered but if it has I can't find it anywhere. So, from the begining then.  Lets say I had a member table and in it I wanted to store what their top 3 interests are.  Their$ row has all the usual things to identify them userID and password etc.. and I had a further 3 columns which were labled top3_1 top3_2 & top3_3 to put each of their interests in from a post form.

If instead I wanted to store this data as a PHP Array instead (using 1 column instead of 3) is there a way to store it as readable data when you open the PHPmyadmin?

At the moment all it says is array and when I call it back to the browser (say on a page where they could review and update their interests) it displays 'a' as top3_01 'r' as top3_02 and 'r' as top3_03 (in each putting what would be 'array' as it appears in the table if there were 5 results. Does anyone know what I mean?

For example -

If we had a form which collected the top 3 interests to put in a table called users,

Code: [Select]
<form action="back_to_same_page_for_processing.php" method="post" enctype="multipart/form-data">
   
    <input name="top3_01" type="text" value="enter interest number 1 here" />
    <input name="top3_02" type="text" value="enter interest number 2 here" />
    <input name="top3_03" type="text" value="enter interest number 3 here" />
    <input type="submit" name="update_button" value=" Save and Update! " />

   
    </form> // If my quick code example for this form is not correct dont worry its not the point im getting at :)
And they put 'bowling' in top3_01, 'running' in top3_02 and 'diving' in top3_03 and we catch that on the same page with some PHP at the top -->

Code: [Select]
        if (isset($_POST)['update_button']) {
   
    $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars
    $top3_02 = $_POST['top3_02']; // i.e, 'running'
    $top3_03 = $_POST['top3_03']; // i.e, 'diving'

With me so far? If I had a table which had 3 columns (1 for each interest) I could put something like -

  Code: [Select]
   include('connect_msql.php');
   
    mysql_query("Select * FROM users WHERE id='$id' AND blah blah blah");
   
    mysql_query("UPDATE users SET top3_01='$top3_01', top3_02='$top3_02', top3_03='$top3_03' WHERE id='$id'");


And hopefully if ive got it right, it will put them each in their own little column. Easy enough huh? But heres the thing, I want to put all these into an array to be stored in the 1 column (say called 'top3') and whats more have them clearly readable in PHPmyadmin and editable from there yet still be able to be called back an rendered on page when requested.

Continuing the example then, assuming ive changed the table for the 'top3' column instead of individual colums,  I could put something like this -


Code: [Select]
            if (isset($_POST)['update_button']) {
       
        $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars
        $top3_02 = $_POST['top3_02']; // i.e, 'running'
        $top3_03 = $_POST['top3_03']; // i.e, 'diving'
   
    $top3_array = array($top3_01,$top3_02,$top3_03);
   
    include('connect_msql.php');
   
    mysql_query("UPDATE members SET top3='$top3_array' WHERE id='$id' AND blah blah blah");
But it will appear in the column as 'Array' and when its called for using a query it will render the literal string. a r r in each field instead.  Now I know you can use the 'serialize()' & 'unserialize()' funtcions but it makes the entry in the database practically unreadable. Is there a way to make it readable and editable without having to create a content management system?

If so please let me know and I'll be your friend forever, lol, ok maybe not but I'd really appreciate the help anyways.  The other thing is, If you can do this or something like it, how am I to add entries to that array to go back into the data base?

I hope ive explained myself enough here, but if not say so and I'll have another go.  Thanks very much people,  L-PLate (P.s if I sort this out on my own ill post it all here)

I have following piece of code below, and I would like to be able to express it pure SQL, something that could go into a .sql file :

$request_string='SELECT topic_forum_id FROM topic_table WHERE topic_id= 2014';
$query=database->prepare($request_string);
$query->execute();
$data=$query->fetch();
$query->closeCursor();
$forum=$data['topic_forum_id'];

$request_string='INSERT INTO post_table
(post_topic,post_forum) VALUES (2014,:forum)';
$query=database->prepare($request_string);
$query->bindValue(':forum',$forum,PDO::PARAM_INT);
$query->execute();
$data=$query->fetch();
$query->closeCursor();

  Is it possible ?

So i have this php as shown below. It should make a list of comments with comment replies below their comment respectively.

The problem is that it only goes through and shows 1 comment and all the comment replies for that one comment. It should be showing all comments i have in the db for that article.

If i remove the second while then it shows all the comments correctly but no comment replies then...

How do i get this script to loop through the db for every comment but also loop through every comment reply for that $row[id]?

If anyone has a better / more efficient way of what I am trying to do, please explain or show example (i am open to anything)...

Code: [Select]
// what article are we showing?
$article_to_show_id = $_GET['article_id'];


$active_is_set_text = "1"; // Active Column text that makes it okay to show

// Finding the article
$search_for_article = mysql_query("SELECT * FROM articles WHERE id = '$article_to_show_id' AND active = '$active_is_set_text'");

while($row = mysql_fetch_array($search_for_article))
{
// format the last updated date right
$update_date_edit = $row[update_date];

$update_date_edit = date('F j, Y \a\t h:ia', $update_date_edit);

$row[update_date] = $update_date_edit;



// format the submit updat date right
$submit_date_edit = $row[submit_date];

$submit_date_edit = date('F j, Y \a\t h:ia', $submit_date_edit);

$row[submit_date] = $submit_date_edit;


echo '
<div>
', $row[title] ,'
</div>


<div>
by: ', $row[author] ,' on ', $row[submit_date] ,'
</div>



<div>
', $row[content] ,'
</div>


<div>
Last Updated: ', $row[update_date] ,'
</div>

<form action="article_reply.php" method="post">
<input type="hidden" name="article_id" value="', $row[id] ,'" />
<button name="article_reply" type="submit" value="submit">Reply</button>
</form>

';

}

$comment_count = 0;
$comment_reply_count = 0;

// Finding all of the comments
$search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '0'");

while($row_comment = mysql_fetch_array($search_for_article))
{
// format the submit updat date right
$comment_date_edit = $row_comment[comment_date];

$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

$row_comment[comment_date] = $comment_date_edit;

echo '
<br>
<br>
COMMENT:<br>

<div>
By: ', $row_comment[username] ,' on ', $row_comment[comment_date] ,'
</div>

<div>
', $row_comment[comment] ,'
</div>
';
$comment_count++;

// Finding all of the comment replies if any
$search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '$row_comment[id]'");

while($row_two = mysql_fetch_array($search_for_article))
{
// format the submit updat date right
$comment_date_edit = $row_two[comment_date];

$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

$row_two[comment_date] = $comment_date_edit;

echo '
<br>
<br>
COMMENT REPLY:<br>

<div>
By: ', $row_two[username] ,' on ', $row_two[comment_date] ,'
</div>

<div>
', $row_two[comment] ,'
</div>
';
$comment_reply_count++;
}
}

Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it?

Thanks For Your Help Guys! 

Hey, I was wondering.. Is there any php functions or any way I can get some information from MySQL?
I want to be able to echo out the uptime for MySQL, and some other things.. I've seen it done before so I know its possible lol.