Array? Join? Or Something Else?

PHP
1

I’m using PHP/MySQL. I’m new to both.

I have two tables:

one table has a field storyType (int).

The other table has the ID (int) and the storyName (varchar). I need to assign a value to a variable based on the storyType integer of table one and the storyName varchar of table two.

I tried to create a switch (select case) from a recordset by using the Dreamweaver repeat region, but that did not work.

any help is greatly appreciated.

2

Solved! I was able to use an Array to accomplish the task.

3

Try

 
SELECT ID,storyType, storyName
FROM table1
 LEFT JOIN table2
   ON table1.storyType = table2.ID
4

Thanks Mandes,

How would I display the results?

5 

 
$sql = mysql_query("SELECT ID,storyType, storyName
                              FROM table1
                                 LEFT JOIN table2
                                     ON table1.storyType = table2.ID
                           ");
 
while ($row = mysql_fetch_array($sql)){
      echo $row['ID'] . ' - ' . $row['storyType'] . ' - ' . $row['storyName'];
}
6

Here’s what I have so far:

$query = "SELECT neoOrderDetails., neoStoryMain. ".
"FROM neoOrderDetails, neoStoryMain ".
“WHERE neoOrderDetails.orderID = 47 AND neoStoryMain.ID = 5”;

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row[‘mainStory’];
echo “<br />”;
}

I need to replace the two numbers 47 and 5 with variables that are set from other recordsets on the page.

7

Nevermind, got it:

$neoOrderID = $row_rsSumm[‘ID’];
// Make a MySQL Connection
// Construct our join query
$query = "SELECT neoOrderDetails., neoStoryMain. ".
"FROM neoOrderDetails, neoStoryMain ".
“WHERE neoOrderDetails.orderID = $neoOrderID AND neoStoryMain.ID = neoOrderDetails.mainProgram”;

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row[‘mainStory’];
echo “<br />”;
}
?>

8

Glad you sorted it.

Two little pointers for going forwards

Never use * in your selects (unless you are planning on using ALL the fields in your tables. It can make the tables returned very big taking more time and also using more resouces. Its better to name the actual fields you are going to use.

Your join should be in the format ‘JOIN table ON comparison’ as that is the new best practice, again it makes the result table less large I believe than joining the tables then sorting with a WHERE statement, someone will correct me if Im mistaken on this.

9

no, that’s a myth :slight_smile:

mysql will perform both equally fast (depending on indexes) but JOIN syntax is easier to understand and to maintain

10

Thanks for clearing that up :blush:

11

The following code works fine when running independently on a test page. However, when I add it into the actual page I need it to function in, it does not display anything. The page is generated and emailed upon a successful transaction so it’s a bit difficult to test.

Here is the code snippet:

<?php
$neoArmenID = $row_rsSumm[‘ID’];
// Make a MySQL Connection
// Construct our join query
$query = "SELECT neoOrderDetails., neoStoryMain. ".
"FROM neoOrderDetails, neoStoryMain ".
“WHERE neoOrderDetails.orderID = $neoArmenID AND neoStoryMain.ID = neoOrderDetails.mainProgram”;

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row
echo "Program(s): ";
while($row = mysql_fetch_array($result)){
echo $row[‘mainStory’];
echo ", ";
}
?>

I can send a private message with the full code if anyone is willing to help.

12

What errors if any is PHP giving when the code is used in the page you need it in?

A couple of quick tips:

$query = "
    SELECT
          neoOrderDetails.*
        , neoStoryMain.*
    FROM
        neoOrderDetails
    INNER JOIN
        neoStoryMain
    ON
        neoOrderDetails.orderID = $neoArmenID
    WHERE
        neoStoryMain.ID = neoOrderDetails.mainProgram
";

Try to space out the queries making use of tabs and leading commas, see above for an example. It doesn’t have any affect on the execution but makes it more easily readable for yourself or anyone working on the code.

Also try to use the proper JOIN syntax

13

yeah, including getting the ON and WHERE clauses the right way round :wink:

change this –

 FROM
        neoOrderDetails
    INNER JOIN
        neoStoryMain
    ON
        neoOrderDetails.orderID = $neoArmenID
    WHERE
        neoStoryMain.ID = neoOrderDetails.mainProgram

to this –

 FROM
        neoOrderDetails
    INNER JOIN
        neoStoryMain
    ON
        neoStoryMain.ID = neoOrderDetails.mainProgram
    WHERE
        neoOrderDetails.orderID = $neoArmenID

:slight_smile:

14

Thanks Guys!