help creating a mysql table for random data retrival

Bill Withers · August 2, 2012

hello all,

Currently I am doing this via txt files and it works fine, but I want to save space/resources and along the way learn something.

I am having a problem creating a table to retrieve random data with php.

I am not sure how to setup the table structure

Here's what I tried so far

table info

CREATE TABLE courses (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  Course_name varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=12 ;

Then I insert the data

INSERT INTO courses (ID, Course_name)VALUES(1, 'Sawgrass');
INSERT INTO courses (ID, Course_name)VALUES(2, 'Riviera CC');
INSERT INTO courses (ID, Course_name)VALUES(3, 'Bethpage');
INSERT INTO courses (ID, Course_name)VALUES(4, 'Torrey Pines');
INSERT INTO courses (ID, Course_name)VALUES(5, 'Sheshan Golf Club');
INSERT INTO courses (ID, Course_name)VALUES(6, 'St Andrews');
INSERT INTO courses (ID, Course_name)VALUES(7, 'Pebble Beach');
INSERT INTO courses (ID, Course_name)VALUES(8, 'Harbour Town');
INSERT INTO courses (ID, Course_name)VALUES(9, 'Oakmont');
INSERT INTO courses (ID, Course_name)VALUES(10, 'Bay Hill');
INSERT INTO courses (ID, Course_name)VALUES(11, 'refstack CC);
INSERT INTO courses (ID, Course_name)VALUES(12, 'Cove Beach');

Then I try to retrieve this data randomly and put into a string $course

<?php

    include ('db.php');

    $connection = new createConnection(); //i created a new object

    $connection->connectToDatabase(); // connected to the database




  $linkid = $_GET['ID'];          
$query  = "SELECT *  FROM course_name order by rand() limit 1";
$result = mysql_query($query);
$course= $row ['course_name'];


?>

Then try to put this into a table

<td><font face="Arial, Helvetica, sans-serif"><?php echo $course; ?></font></td>

It errors out saying there is nothing in the database to retrieve.

Would be grateful if someone could point me in the right direction

php 5.4

mysql 5.5

mikosiko · August 2, 2012

between this 2 lines:

$result = mysql_query($query);
$course= $row ['course_name'];

you must to use some of the mysql_fetch_....() functions to .. well... fetch your data and give some content/meaning to $row

mysql_fetch_assoc() per example http://php.net/manual/en/function.mysql-fetch-assoc.php

Bill Withers · August 2, 2012

Thank you! This worked

$query = "SELECT course_name FROM courses ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$course = $row["course_name"];

Bill Withers · August 3, 2012

Well as I thought, I must have the tables set wrong. I have about 12 calls the the db for randomized data. if I use the select like this:

$query = "SELECT course_name, pin, tee, wind, fs, gs  FROM courses, pin, tee, wind, fs, gs  ORDER BY RAND()LIMIT 1";

And fill it up with my requests, It chokes after adding more than 8 table query's.

But if I split it up like I will show below, It works, but is only slightly faster than just using text files.

There has to be a better more efficient way of doing this but I could use a few pointers in the right direction

Thanks in advance

heres the rest of the ugliness

$query = "SELECT course_name, pin, tee, wind, fs, gs  FROM courses, pin, tee, wind, fs, gs  ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$course = $row["course_name"];
$pin = $row["pin"];
$wind = $row["wind"];
$tee = $row["tee"];
$fs = $row["fs"];
$gs = $row["gs"];


$query = "SELECT gh FROM gh ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$gh = $row["gh"];

$query = "SELECT rl FROM rl ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$rl = $row["rl"];

$query = "SELECT st FROM st ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$st = $row["st"];

$query = "SELECT am FROM am ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$am = $row["am"];

$query = "SELECT pg FROM pg ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$pg = $row["pg"];
$query = "SELECT pre FROM pre ORDER BY RAND()LIMIT 1";
$result = mysql_query($query) or trigger_error(mysql_error()." ".$query);
$row = mysql_fetch_assoc($result);
$pre = $row["pre"];
?>

And here is how the results are displayed:

<table border="0" width="100%" cellpadding="3" cellspacing="3">
<tr>
	<td><b>Courses</b></td>
	<td><b>Tee Box</b></td>
	<td><b>Wind Speed</b></td>
	<td><b>Pin Location</b></td>
	<td><b>Fairway Speed</b></td>
	<td><b>Green Speed</b></td>
	<td><b>Green Hardness</b></td>
	<td><b>Rough Length</b></td>
	<td><b>Swing Type</b></td>
	<td><b>Aiming Mode</b></td>
	<td><b>Putting Grid</b></td>
<td><b>Previews</b></td>
</tr>
	<td><?php echo $course; ?></td>
	<td><?php echo $tee; ?></td>
	<td><?php echo $wind; ?></td>
	<td><?php echo $pin; ?></td>
	<td><?php echo $fs; ?></td>
	<td><?php echo $gs; ?></td>
	<td><?php echo $gh; ?></td>
	<td><?php echo $rl; ?></td>
	<td><?php echo $st; ?></td>
	<td><?php echo $am; ?></td>
	<td><?php echo $pg; ?></td>
	<td><?php echo $pre; ?></td>

Bill Withers · August 3, 2012

Yep, this isnt a very good DB structure for pulling random data.

1 	id 	    select_type 	table 	type 	possible_keys 	key 	     key_len 	ref 	rows 	       filtered 	   Extra
1 	SIMPLE 	pin 	           ALL 	NULL	NULL	         NULL	        NULL	                           4 	           100.00 	    Using temporary; Using filesort
1 	SIMPLE 	tee 	           ALL 	NULL	NULL	         NULL        	NULL	                           4 	           100.00      Using join buffer
1 	SIMPLE 	wind 	           ALL 	NULL	NULL	         NULL        	NULL	                           4 	 	   100.00      Using join buffer
1 	SIMPLE 	fs 	           ALL 	NULL	NULL	         NULL	        NULL	                           4 	           100.00 	    Using join buffer
1 	SIMPLE 	gs 	           ALL 	NULL	NULL	         NULL	        NULL	                           4 	           100.00 	    Using join buffer
1 	SIMPLE 	courses 	   ALL 	NULL	NULL	         NULL	        NULL	                           23 	   100.00 	    Using join buffer

I tried to build a table that used all data in different columns but it kept returning null data sometimes. I read that

building a table with a ID1 (ID)2 etc was the best way to go but I am not sure.

Any ideas on this?

DavidAM · August 4, 2012

$query = "SELECT course_name, pin, tee, wind, fs, gs  FROM courses, pin, tee, wind, fs, gs  ORDER BY RAND()LIMIT 1";

You are selecting data from 6 tables with NO JOINS AT ALL. This will result in a cartesian product. So for each of the 23 rows in courses, you get all 4 of the gs rows, and for each of those you get all 4 of the fs rows, and for each of those you get all 4 of the wind rows ...

That's 23 * 4 * 4 * 4 * 4 * 4 rows = 23,552 rows. Which are then sorted randomly, so one row can be returned.

You say you call that 12 times. If you really need random rows from a cartesian product, why don't you change the LIMIT to 12, so you only do the table scans and sorts one time?

In general, ORDER BY RAND() is not healthy, and will not scale well. That is, if you get 2300 courses (and associated data) in your database, it will run significantly slower than it is now.

Cartesian products are also not healthy and don't scale.

There is not enough information about your database to offer an alternative, but I suggest you need one.

Bill Withers · August 4, 2012

Thanks for replying, it had been a very long time since I messed with ,MySQL query's and table creation.

I read as much as I could about creating a database just to pull random data, I found a few sites to look at but they only

started me on the path that this mess is in now. I tried to wrap my mind around the Join statements and after about 11 hours I gave up.

I will supply any info you need to get me on the right track.

I tried to add the other data into the courses table but because of column id which is key, when I added the data, it got placed below the last course data, then the next below that etc... so at the start of pin data there are 24 blank rows in its column and the output would include these null 24 items so that didnt work

I then created the twelve other tables and here I am. What info would you require?

Thanks again

fenway · August 4, 2012

How about table structures?

Bill Withers · August 4, 2012

Thanks

here is the dump

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE am (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  am varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE courses (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  Course_name varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE fs (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  fs varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE gh (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  gh varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE gs (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  gs varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE pg (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  pg varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE pin (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  pin varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE pre (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  pre varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE rl (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  rl varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE st (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  st varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE tee (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  tee varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE wind (
  ID smallint(6) NOT NULL AUTO_INCREMENT,
  wind varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
SET FOREIGN_KEY_CHECKS=1;

fenway · August 5, 2012

Ok -- so what's wrong with one rand query per table? Not that I understand what's in each one.

Bill Withers · August 5, 2012

Thanks for answering,

I guess nothing, It's just less efficient (load time wise) than using txt files that I am doing now.

fenway · August 5, 2012

Thanks for answering,

I guess nothing, It's just less efficient (load time wise) than using txt files that I am doing now.

A random row from a file shouldn't be any faster.

Sign In

help creating a mysql table for random data retrival

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information