PHP - Moved: Selct Data From 2 Mysql Tables Together

Hi all

I have 3 tables
Table_1, Table_2 and Table_3

Table_1 is a list of countries, with name and country_id
Table_2 is a table that has 3 fields, id, name and description
Table 3 is a table that has name, Table_2_id

So what I need to do:
Display the name and description field of Table_2 in a form
Loop through the countries table and display each as an input box and display on the same form
When I fill out the form details, the name/description must be inserted into Table_2, creating an id
The input boxes data then also needs inserting into Table_3, with the foreign key of Table_2_id

So a small example would be:
Name: testing
Description:  this is a test
Country of Australia: Hello
Country of Zimbabwe: Welcome

This means that in Table_2, I will have the following:
=============================
| id   | name    | description
| 1    |  testing | this is a test
=============================

Table_3
=============================
| Table_2_id |  name         | country_id
| 1                    | Hello          | 20
| 1                    | Welcome  | 17
=============================

20 is the country_id of Australia
17 is the country_id of Zimbabwe

Code:

Generating the input fields dynamically:

                           $site_id = $this->settings['site_id'];

                           $options = '';
                           $country_code = '';
   $query = $DB->query("SELECT country_code, country_id, IF(country_code = '".$country_code."', '', '') AS sel FROM Table_1 WHERE site_id='".$this->settings['site_id']."' ORDER BY country_name ASC");
   
                               foreach ($query->result as $row)
                               {
                                        $options .= '<label>' . 'Test for ' . $this->settings['countries'][$row['country_code']] . '</label>' . '<br />';
                                        //$row['country_id'] is the country_id from Table_1
                                        $options .= '<input style="width: 100%; height: 5%;" id="country_data" type="text"  name="' . $row['country_id'] . '"  value="GET_VALUE_FROM_DB" />' . '<br /><br />';
                               }
                              echo $options;


This outputs:
Code: [Select]
Textareas go here......
<label>Test for Australia</label>
<input type="text" value="" name="20" id="country_data" style="width: 100%; height: 5%;">
<label>Test for Zimbabwe</label>
<input type="text" value="" name="17" id="country_data" style="width: 100%; height: 5%;">

Now, I need to insert the value of the input field and it's country_id (20 or 17) into Table_3 and also Table_2_id.
This then means I could get the value from Table_3 to populate 'GET_VALUE_FROM_DB'

But I'm at a loss on how I'd do this.

Could someone help me with this?

Thanks

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to Application Design.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to Other Libraries and Frameworks.

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

Hello to all,

I have problem figuring out how to properly display data fetched from MySQL database in a HTML table.

In the below example I am using two while loops, where the second one is nested inside first one, that check two different expressions fetching data from tables found in a MySQL database. The second expression compares the two tables IDs and after their match it displays the email of the account holder in each column in the HTML table.

The main problem is that the 'email' row is displayed properly while its while expression is not nested and alone(meaning the other data is omitted or commented out), but either nested or neighbored to the first while loop, it is displayed horizontally and the other data ('validity', 'valid_from', 'valid_to') is not displayed.'

Can someone help me on this, I guess the problem lies in the while loop?
Here is part of the HTML code:

<thead>
 <tr>
 <th data-column-id="id" data-type="numeric">ID</th>
 <th data-column-id="email">Subscriber's Email</th>
 <th data-column-id="validity">Validity</th>
<th data-column-id="valid_from">Valid From</th>
 <th data-column-id="valid_to">Valid To</th>
</tr>
</thead>

Here is part of the PHP code:

 

    <?php
    while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo '
            <tr>
                <td>'.$row["id"].'</td>
        ';
        while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
        echo '
                <td>'.$row1["email"].'</td>
        ';
        }
        if($row["validity"] == 1) {
            echo '<td>'.$row["validity"].' month</td>';
        }else{
            echo '<td>'.$row["validity"].' months</td>';
        }
        echo '  <td>'.$row["valid_from"].'</td>
                <td>'.$row["valid_to"].'</td>
        </tr>';
    }
    ?>

P.S. Please know that I have purposely omitted the connection part and the methods in the class that are used in this code to keep the spot of the problem as clean as possible. If you need me to rephrase the question, please ask.

Thank you.

This could be PHP or MySql so putting it in PHP forum for now...
I have code below (last code listed) which processes a dynamically created Form which could have anywhere from 0 to 6 fields.  So I clean all fields whether they were posted or not and then I update the mySQL table.

The problem with this code below is that if, say, $cextra was not posted (i.e. it wasnt on the dynamically created form), then this code would enter a blank into the table for $cextra (i.e. if there was already a value in the table for $cextra, it gets overwritten, which is bad).

What is the best way to handle this?  I'm thinking i have to break my SQL query into a bunch of if/else statements like this...

Code: [Select]
$sql = "UPDATE cluesanswers SET ";
if (isset($_POST['ctext'])){
echo "ctext='$ctext',";
} else {
//do nothing
}
and so on 5 more times....

That seems horribly hackish/inefficient.  Is there a better way?

Code: [Select]
if (isset($_POST['hidden']) && $_POST['hidden'] == "edit") {

$cimage=trim(mysql_prep($_POST['cimage']));
$ctext=trim(mysql_prep($_POST['ctext']));
$cextra=trim(mysql_prep($_POST['cextra']));
$atext=trim(mysql_prep($_POST['atext']));
$aextra=trim(mysql_prep($_POST['aextra']));
$aimage=trim(mysql_prep($_POST['aimage']));


//update the answer edits
$sql = "UPDATE cluesanswers SET ctext='$ctext', cextra='$cextra', cimage='$cimage', atext='$atext', aextra='$aextra', aimage='$aimage'";
$result = mysql_query($sql, $connection);
if (!$result) {
die("Database query failed: " . mysql_error());
} else {
}

hi guys, im new to this forum

I'm new also to php, I need help from you guys:

I want to display personal information from a certain person (the data is on the mysql database) using his name as a link:

example:
               (index.php)
               names
                          1. Bill Gates
                          2. Mr. nice Guy
           
i want to click Bill Gates
 
   (output.php)
     Name: Bill Gates
     Country:xxxx
     Age: xx   
                 etc.

How can i make this or how to learn this?
   
                 

             
               

   
       

Hi I am trying to select and order data/numbers from a colum in a mysql data base however i run the code and it returns no value just a blank page no errors or any thing so i think the code is working right but then it returns no result? Please help thanks

Here is the code:

<?php
$host= "XXXXXX";
$mysql_user = "XXXXXX";
$mysql_password = "XXXXXX";
$mysql_database = "XXXXXXX";

$connection = mysql_connect("$host","$mysql_user","$mysql_password") or die ("Unable to connect to MySQL server.");
mysql_select_db($mysql_database) or die ("Unable to select requested database.");

$row = mysql_fetch_assoc( mysql_query( "SELECT XP FROM Game ORDER BY number DESC LIMIT 1" ) );
$number = mysql_result(mysql_query("SELECT XP FROM Game ORDER BY number DESC LIMIT 1"), 0);

echo "The the highest XP is $number";

?>

Hi guys,

This is my first time posting here - im just getting into PHP - i got a question;

I have two databases:

profile(id, name, interests, dob, gender, join_date, email)
interests(id, profile_id, interests)
id being the primary key, and profile_id being the foreign key from profile.

I want to script that returns profile information and all the matching interests (one user can have multiple interests).

This is what i have so far, though it does not work, and i knew it wouldnt;

function get_profile($id) {
   $connection = mysql_open();
   $query = "SELECT * ";
   $query .= "FROM profiles, interests ";
   $query .= "WHERE profile.id=" . $id;
   $query .= " AND interests.profile_id=" . $id;
   $result = @ mysql_query($query, $connection);
   // Transform the result set to an array (for Smarty)
   $entries = array();
   while ($row = mysql_fetch_array($result)) {
      $entries[] = $row;
   }
   mysql_close($connection) or show_error();;
   return $entries;   
}


Can someone please advise on how this can be done? or do i need to have two query's one for each table ?

Thank you in advance!!