MYSQL and PHP, how do I show a random group of records where count >1

I've just added to my favourite #fridayflash list , a featured Author selection. Where I have been adding stories each week, there are naturally several authors appearing more than once. What I wanted to do, was feature one of these author on the page, by random selection, and list their stories.

I apologies if the language might seem a bit funny in this post, but from trying to do it, it was not what I quite expected and took a bit of googling. I am typing this post this way, in the hope someone with the same problem finds it easy on google.

Firstly my data is stored in the following table structure.


CREATE TABLE IF NOT EXISTS `fridayflash` (
  `sID` int(11) NOT NULL auto_increment,
  `week` int(11) NOT NULL,
  `sortA` varchar(1) NOT NULL,
  `title` varchar(200) NOT NULL,
  `author` varchar(200) NOT NULL,
  `twitter` varchar(200) NOT NULL,
  `link` varchar(200) NOT NULL,
  `review` varchar(200) NOT NULL,
  `notes` varchar(200) NOT NULL,
  PRIMARY KEY  (`sID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;

The first thing I needed to was find out all of those authors who had more than one entry in the database. My SQL looked liked the following *

SELECT author
FROM fridayflash
GROUP BY author
HAVING COUNT( sID ) >1


The next thing, was to limit by select one of these records by random. To achieve this, I added the following two lines to the bottom of the query. 


SELECT author
FROM fridayflash
GROUP BY author
HAVING COUNT( sID ) >1
ORDER BY RAND( )
LIMIT 1


I now then wanted to select all the stories by my random selection and this is where I began to run into problems.  Firstly approaching this as if i was using Oracle of SQL Server and using nested queries, HAVING or WHERE. I ran into the error

'This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'


I found my solution here on this article on sub queries  and got the required result, by using the following SQL

SELECT *
FROM fridayflash
INNER JOIN (

SELECT author AS b
FROM fridayflash
GROUP BY author
HAVING COUNT( sID ) >1
ORDER BY RAND( )
LIMIT 1

) AS c ON fridayflash.author = c.b



I then created the following PHP Function **
function FeaturedAuthor()
{
$counter = 1;

$sql = "SELECT * FROM fridayflash
INNER JOIN (
SELECT author AS b
FROM fridayflash
GROUP BY author
HAVING COUNT( sID ) >1
ORDER BY RAND( )
LIMIT 1) AS c
ON fridayflash.author = c.b";

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

if (mysql_num_rows($result) == 0)
{
echo "<em>No created.</em>";
} else {


while ($row = mysql_fetch_array($result))
{
If ($counter == 1)
{
echo "<h1>" ."<a href=http://twitter.com/" . $row['twitter'] .">". $row['author'] . " <a>" ."</h1>";
}

echo "<p><a href=" . $row['link'] . ">" . $row['title'] ."</a> " .
$row['review'] . "</i></p>";

$counter = $counter ++;
}
}


}




And you can see the result on the #fridayflash list page of my site, just under the Most Recent stories added. If you hit F5 you will see the featured author change.

*Yes, I know using author name is not an ideal key :-p, let me tell you sometime of a horror story involving a developer being allowed free roam on the company servers without any DBA's to distract you.
** I'm very slowly learning php, I am not happy with the use of the counter in the above to make sure the author name is only outputted once.  Suggestions ? 

Another quick tidy up on My #fridayflash page

I have been editing a friends wedding video recently, so have not had much time to continue with learning php and messing around with chance1234.com

However, I have done a very quick tidy up on the fridayflash page which lists my favourite reads. Nothing drastic, just shifted the last ten and archive lists from out of the two columns and moved them below.

I may introduce some sorting and filtering of the archive when it gets bigger; I am, however curious in what I can do to treat the data more "equal" and what I mean by equal is a story from say week 22 should be as prevalent as a story from week 36.

I have the random selection in the corner, and by the nature of listing them alphabetically they are getting mixed up, but I believe there are more tricks out there. One of the faults I see with blogs is that information gets too easily buried and quite a few things I'm trying out on chance1234.com is ways of getting on a more even footing - hence the random order on the front page.

Simple insert into mysql from html form via php example

*** Disclaimer if you think the internet is there to do all the work for you, then this post is not for you, go and enjoy a chat with your neighbour  ***

Just been spending a bit more time learning php and on the future recipe section for my chance1234.com site. I wanted a clean and simple INSERT INTO example which I can use as a basis for future development.

In the php book I am reading I have tons of examples and on the web, there are tons of examples - but!! A lot of them have lots of other bits and pieces cluttering them up. In good time, as I learn more I am sure it will begin to make sense. But, I think a lot of people who write tutorials forget who they are writing for. Putitng in ISSET() might be second nature for yourself, but if you do not explain why you are putting it there, then it is just confusion.

Also, Yes sometimes the most simple solutions are not the right solution, but for someone learning, stripping back to the essentials can be most beneficial so they can see clearly what is going on. Remember making mistakes is one of the best ways of learning.

Anyway, end of rant and onto my insert.

I have a php file for adding recipes to my database. It currently looks something like this and is tentatively named add.php

<?php
require_once 'funcLists.php';
?>

<form action="transRecipe.php" method="POST">

<h1>Add Recipes</h1>

<p>Recipe Title
  <input type="text" size=50 name="recipeTitle" maxlength="255"  value="">
</p>

<p>Recipe Type
  <?php listRecipesTypes(); ?>   
  </p>
 
<p>Quick Description<br>
    <textarea name="recipeQuick" rows="6" cols="63"></textarea>
</p>

<p>Select Ingredients</p>
    <?php listIngredients() ?>   
<br>

<input type="submit" type="submit" value="Add Recipe">   

</form>

I also have a second php file called transRecipes.php , which contains my INSERT INTO code and that looks like this.

<?php

require_once '../conn.php';


        $sql = "INSERT INTO recipe (rType,title,quick)
        VALUES
        ($_POST[recipeType],'$_POST[recipeTitle]','$_POST[recipeQuick]')";
       
       
        echo $sql;
         echo "<br><br>";
       
        if (!mysql_query($sql))
          {
          die('Error: ' . mysql_error());
          }
        echo "Here is my bottom";

?>

And here is a list of the important condsiderations

1. In the form action part , there is the reference to the transRecipe.php file , which you need for it to work
   
2. The $POST[...] parts in transRecipe refer to the names of the input controls on the Add.php file
3. The echo parts are there to help me debug and allow us to concentrate on getting the SQL right in the INSERT statement

Creating a Multiselect box from mysql source

*** Disclaimer, If you are the type of person who thinks the internet owes you everything then this post is probably not for you. Read a book for a change ***

Just adding these disclaimers, as this blog is more my thought process as I am teaching myself php. I make no claims for anything being the best or right way to do things.

One of the main reason for these posts, is so I cam easily copy pasta bits no matter where I am.

Anyhow, going back to my recipe section on my site that I'm building, I want on the form a multiple select box for the ingredients. I have the following table on my database


CREATE TABLE IF NOT EXISTS `ingredients` (
`iId` int(11) NOT NULL auto_increment,
`type` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`notes` varchar(255) NOT NULL,
PRIMARY KEY (`iId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

Just a note about why I have type in my ingredients. This is one of my bugbears with a lot of recipe listingsis they never group the ingredients by type. I am including type so that you can copy down the recipe and go to the supermarket and be less likely to miss anything off.

The Function for the multibox select is as follows

Function listIngredients()
{
$html ='<select name="Ingr[]" multiple="multiple">';
$sql=" SELECT `iId` , `name`
FROM `ingredients`
ORDER BY TYPE , name
LIMIT 0 , 30 ";

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

if (mysql_num_rows($result) == 0)
{
echo "<em>No created.</em>";
}
else
{
while ($row = mysql_fetch_array($result))
{
$html .= '<option value='. $row['rId'].'>'.$row['name'].'</option>';
}
}
$html .= '</select>';
echo $html;
}

Adding items to a dropdown from a MYSQL Database

*** Disclaimer I do not know if this is the best method or is even correct, if you are the type of person who reads things on the internet, tries them out and then gets annoyed when things don't work out. Then maybe this post is not for you, go outside instead and enjoy some fresh air ***

My next php project is, I'm building a small recipe section for my chance1234.com site. I've started on working on a form to allow myself to add recipes to the database.

One of the options I want when adding recipes is to be able to clarify recipes by type. ie pasta dishes, soups etc

What I did was set up a table on mysql database called rtype


CREATE TABLE IF NOT EXISTS `rType` (
`rId` int(11) NOT NULL auto_increment,
`type` varchar(255) NOT NULL,
`Notes` varchar(255) NOT NULL,
PRIMARY KEY (`rId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Then on my php file, I created the following function

Function listRecipesTypes()
{
$html ='<select name="'.$name.'">';
$sql="SELECT rID,type FROM rType";

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

if (mysql_num_rows($result) == 0)
{
echo "<em>No created.</em>";
}
else
{
while ($row = mysql_fetch_array($result))
{
$html .= '<option value='. $row['rId'].'>'.$row['type'].'</option>';
}
}
$html .= '</select>';
echo $html;
}

?>


I adapted the code from a Make a Website post. Ideally what I want to do is adapt the function so I can chuck any array at it.

#FlashFriday Update

Had a bit of a tidy up on my #fridayflash page on chance1234.com

Firstly I had all the SQL for the queries on the page, I took them out and placed them in a separate php file and organised them into functions. This has had a noticeable improvement on the speed of loading up the page.

I also added some <hr> tags to make the different sections a bit clearer.

I am a bit stuck on what my next move will be for this page; a couple of thoughts

1) I want to make the random selection, more prominent
2) I need a way of organizing the archive, whilst treating all entries equal !

The New Front Page

Very basic at the moment, but its the direction I want to go. Have a database table sitting in the background holding link information and I'm selecting this in a query using the rand() function.

As I pull this information back, I am then creating an HTML table with this information in and using a counter to start a new row, every 3 items.

Not sure if i will use thumbnails, banners or even just text at the moment, so it is bit of a mix match.