Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I decided to create two separate tables - Reviews and Features - to hold different kinds of data, with each one using joins to the same parent tables.

I decided on this method because the Features table for example doesn't require several of the rows that the Reviews table uses, such as Rating, Cover Art Etc., but I couldn't figure out how to stop them from displaying when I wanted to show a Feature.

Now I'm thinking I should be pulling from just one table and using ifs and elseifs Etc to change the way the data displays because when I went to create a pulldown in the nav section so people can see an index page with all the Music, Hardware, Software, Book Reviews Etc from the Reviews table and an index of all the Interviews, News, Opinion Etc. from the Features table I'm faced with a bigger coding problem than using what I'm hoping could be fixed with if's and else's, although I can't quite get my head around how those work.

Here are the two queries I have so far, and further down is an example each type displays on the page.

mysql_select_db($database_em, $em);
$query_getReview =
"SELECT 
reviews.title,
reviews.cover_art,
reviews.blog_entry,
reviews.rating,
reviews.published,
reviews.updated,
artists.artists_name,
contributors.contributors_name,
contributors.contributors_photo,
contributors.contributors_popup,
categories_name
FROM
reviews
JOIN artists ON artists.id = reviews.artistid
JOIN contributors ON contributors.id = reviews.contributorid
JOIN categories ON categories.id = reviews.categoryid
ORDER BY reviews.updated DESC LIMIT 3";
$getReview = mysql_query($query_getReview, $em) or die(mysql_error());
$row_getReview = mysql_fetch_assoc($getReview);
$totalRows_getReview = mysql_num_rows($getReview);

mysql_select_db($database_em, $em);
$query_getFeature =
"SELECT 
features.features_words,
features.published,
features.updated,
artists.artists_name,
contributors.contributors_name,
contributors.contributors_photo,
contributors.contributors_popup,
categories_name
FROM
features
JOIN artists ON artists.id = features.artistid
JOIN contributors ON contributors.id = features.contributorid
JOIN categories ON categories.id = features.categoryid
ORDER BY features.updated DESC LIMIT 1";
$getFeature= mysql_query($query_getFeature, $em) or die(mysql_error());
$row_getFeature = mysql_fetch_assoc($getFeature);
$totalRows_getFeature = mysql_num_rows($getFeature);

Here's an example of how I'm presenting on the page:

<?php do { ?>
<table id="center_table" width="650" border="0" cellpadding="0" cellspacing="0">
<tr>
    <td width="650" height="30" colspan="3" bgcolor="#FFFFFF"><a class="category" href="/" target="_top"><?php echo $row_getReview['categories_name']; ?></span></td><!-- category -->
</tr>
Etc.
</table>
<?php } while ($row_getReview = mysql_fetch_assoc($getReview)); ?>


<?php do { ?>
<table id="center_table" width="650" border="0" cellpadding="0" cellspacing="0">
<tr>
    <td width="650" height="30" colspan="3" bgcolor="#FFFFFF"><a       class="category" href="/" target="_top"><?php echo $row_getFeature['categories_name']; ?></span></td><!-- category -->
</tr>
<tr>
    <td  width="60" height="60" rowspan="2" bgcolor="#FFFFFF">&nbsp;</td><!-- product photo -->
    <td width="590" height="30" colspan="2" bgcolor="#FFFFFF"><span class="artist"><?php echo $row_getFeature['artists_name']; ?></span><span class="title">Interview</td><!-- artist & title -->
</tr>

</table>
<?php } while ($row_getFeature = mysql_fetch_assoc($getFeature)); ?>

Needless to say this solution just seems like the wrong route, but it at least works.

share|improve this question
    
Is there any reason you're using the dusty, antiquated, deprecated and dangerously insecure by default mysql_query interface? –  tadman Nov 29 '12 at 16:26
    
I'm focussing on getting things working the way I want them to work first before figuring out how to use the new query interface I found out about just two days ago. It's all running in MAMP on my laptop so no security issues yet. –  Paul Seattle Nov 29 '12 at 16:36
    
The mysqli_ functions can be used nearly identically to the mysql_ functions. –  G-Nugget Nov 29 '12 at 16:39
    
Thanks, yes I am aware of it but I just happened to use a tutorial from a book that is a couple years old by David Powers to get to where I'm currently at and didn't want to get too sidetracked changing it to mysqli before moving it all to the server. –  Paul Seattle Nov 29 '12 at 16:47
    
That being said, I suppose if this is all the code I have written (or at least adapted) so far maybe I should start using mysqli before I go any further. I just had a look into using a tool from Oracle to do it but my head nearly exploded after the first paragraph of instructions. –  Paul Seattle Nov 29 '12 at 17:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.