MySQL Select Records & Display in Even Vertical Columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

MySQL Select Records & Display in Even Vertical Columns

Post by lemonfreshmedia »

Hello again,
If you've read any of my previous posts you'll see that I've been trying to format some data using 3 vertical columns.
I've moved away from using xslt and xml and am trying a mysql database.

I'm creating an nhl draft page

How can select a common group (through their Draft round number) from the databse and display in more or less evenly sized columns.
I can get the records i need from the database right now, just can't figure out the syntax to specify to write 10 records in one column, the following 10 in the next column , and the remaing 10 in the last.

Can anyone help tweak my code?

Again, Any help is much apreciated.

PLease ignore the magic mod. I'm trying to get the remainder of division by 3 so as to specify which columns need extra records added on.

Code: Select all

<?php




$host   = 'localhost'; // Hostname of MySQL server
$dbUser = 'xxxx';    // Username for MySQL
$dbPass = 'xxxx';    // Password for user
$dbName = 'drafts'; // Database name

// Make connection to MySQL server
if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {
 die('Could not connect to server');
}

// Select the database
if (!mysql_select_db($dbName, $dbConn)) {
 die('Could not select database');
}


// ... some code here using MySQL

// Close the connection when finished




// Define reusable "chunks" of SQL
$table = " FROM {$_GET['league']}";
$where = " WHERE roundnumber='{$_GET['round']}' AND draftyear='{$_GET['year']}' AND LIMIT='{$row['numrows']/3}' ";


// Query to count the rows returned
$sql = "SELECT COUNT(*) as numrows" . $table . $where;

// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);

$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);


$magicmod = $row['numrows']%3;
$magicdiv = $row['numrows']/3;



// A query to fetch the rows
$sql = "SELECT * " . $table . $where;

// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);









// Fetch rows from MySQL one at a time
		while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {
   
   
  		 echo "<div id=\"pick-odd\"><div id=\"logo\"><img width=\"60\" height=\"60\"src=";
  	
		 echo "\"{$_GET['league']}/". $row['logo']  .".gif\"></div><div id=\"pick-details\">";
  		 echo "<div id=\"draft-number\">Draft#:". $row ['draftnumber']   ."</div>";
  		 echo "<div id=\"player\">". $row['player'] ."</div>";
  		 echo	"Position:". $row['position']   ."<br>";
  		 echo	"Drafted From:".$row['draftedfrom']."<br></div> </div>";
		
}
if ($magicmod == 0){
echo "MAGICMOD = 0!";
	 echo $magicdiv;  
   echo $magicmod;
}
if ($magicmod == 1){
echo "MAGICMOD = 1!";

}
if ($magicmod == 2){
echo "MAGICMOD = 2!";

}





mysql_close($dbConn);

?>
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

please post a link to the working file or draw out how you want the output to be....it is hard to imagine how you want the output formatted....
Last edited by raghavan20 on Wed Jan 25, 2006 9:41 pm, edited 1 time in total.
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

Ok, This is how it should look

Post by lemonfreshmedia »

1 11 21
... ... ...
10 20 30


But someimtes may be 31 or 32.

THanks, SOrry i DIdnt show that earlier
Cheers.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Adapt your code to this one....run this first to see how it works

Code: Select all

<?php
$i = 1;
while ($i < 40) { 
	if ($i%10 == 1){
?>
		<div style = 'float:left; width:100px;'>
<?php	}	?>
		<span style = 'background-color:#FF4323; margin:2px; width:90px; text-align:center;'><?php 

echo $i; ?></span><br />

<?php
	if ($i%10 == 0){
?>
		</div>

<?php	}	
	$i++;
}
?>
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

YEs, this works

Post by lemonfreshmedia »

HOwever my queries sometimes pull up 40 or 100 odd records.

My idea is to take the result (number of records returned) divide by 3

so

if my result was 38 for instance
Using the code posted above i can get

38 % 3 = 2

So now the remainder is 2.

If i can get the number (rounded down) to display the first column

eg. 38/3(how do I specify floor or round to the lowest whole number?)should equal = 12

then i know that the columns should write like the following.

1 (from 1) 13greater than floor(38/3)(rounded down) 26 greater than or equal to ((38/3)*2)+1
..... to ....... .......
floor(38/3)(rounded down) and less than or equal to ((38/3)*2)+1 and less than ((38/3)*3)+2
or 12 in this case or 25 in this case or 38



I should mention that I acheied this using XSLT however,
the way i wrote the script overloaded our server and i was forced to go with a my sql database.I hope im being clear enough.

not sure if i should do multiple database connections for each column.

I have retireved the number of rows using $row['numrows']
but i can't seem to address it the way i want.

I really appreciate your interest and again i would glady provde any flash actiopnscript or animation question or project you have.
I beleive in give and take.

Sincereley.
j
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There are a few threads linked to from Useful Posts (link in signature) that talk about various algorithms and techniques for performing this kind of display.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

just tell me one thing, how many columns do you need in total irrespective of the number of records returned?
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

3 Columns

Post by lemonfreshmedia »

3 columns is all i need.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Code: Select all

<?php
$totalValues = 200; //set this here
//$totalValues = 1000; //try this after you run the first one
$i = 1;
while ($i < $totalValues) { 
	if ($i % ceil($totalValues/3) == 1){
?>
		<div style = 'float:left; width:100px;'>
<?php	}	?>
		<span style = 'background-color:#FF4323; margin:2px; width:90px; text-align:center;'><?php 

echo $i; ?></span><br />

<?php
	if ($i % ceil($totalValues/3) == 0){
?>
		</div>

<?php	}	
	$i++;
}
?>
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

Sweet

Post by lemonfreshmedia »

Your example work perfectly.
I guess I was complicating things

Now I can't seem to address my code properly.
I'm still getting one column.
I can't find out how to count what current row?

Code: Select all

<?php




$host   = 'localhost'; // Hostname of MySQL server
$dbUser = 'xxxx';    // Username for MySQL
$dbPass = 'xxxxx';    // Password for user
$dbName = 'drafts'; // Database name

// Make connection to MySQL server
if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {
 die('Could not connect to server');
}

// Select the database
if (!mysql_select_db($dbName, $dbConn)) {
 die('Could not select database');
}


// ... some code here using MySQL

// Close the connection when finished




// Define reusable "chunks" of SQL
$table = " FROM {$_GET['league']}";
$where = " WHERE roundnumber='{$_GET['round']}' AND draftyear='{$_GET['year']}'";


// Query to count the rows returned
$sql = "SELECT COUNT(*) as numrows" . $table . $where;

// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);

$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);


$magicmod = $row['numrows']%3;
$magicdiv = $row['numrows']/3;
$numberofpicks = $row['numrows'];



// A query to fetch the rows
$sql = "SELECT * " . $table . $where;

// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);









// Fetch rows from MySQL one at a time
		while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {
   if ($magicmod == 1){
   ?>
   <div style = 'float:left; width:100px;'>";
<?php     }    ?>

<span style = 'background-color:#FF4323; margin:2px; width:90px; text-align:center;'> 
<?php  echo $row ['draftnumber']; ?> </span><br />
		 
		 <?php
		if ($magicmod == 0){
		
		?>
		</div>
		<?php 
		echo "THE ANSER IS ZERO";
		}$row++;
		
		    
    
		
		
}
if ($magicmod == 0){
echo "MAGICMOD = 0!";
	 echo $magicdiv;  
   echo $magicmod;
}
if ($magicmod == 1){
echo "MAGICMOD = 1!";

}
if ($magicmod == 2){
echo "MAGICMOD = 2!";

}





mysql_close($dbConn);

?>

      <?php


?>
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Do not use magic mod and magic div,,,it wont work...just insert this code in appropriate place....

Code: Select all

<?php

$numberofpicks = mysql_num_rows($queryResource);
$i = 0; 
while ($row = mysql_fetch_assoc($queryResource) && $i <= $numberofpicks) { 
	if ($i % ($row['numrows']/3) == 1){ 
	?> 
		<div style = 'float:left; width:100px;'>"; 
<?php  
	}    
?> 
	<span style = 'background-color:#FF4323; margin:2px; width:90px; text-align:center;'> 
		<?php echo $row ['draftnumber']; ?> 
	</span><br /> 
	 
	<?php 
	if ($i % ($row['numrows']/3) == 0){ 
	?> 
		</div> 
<?php 
	}
	$i++; 
} 
?>
EDIT: When you post code next time, make sure you align the code properly as I did
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

Nice

Post by lemonfreshmedia »

I hate to post again on this topic but.. here goes
I've tried your snippet in my code trying only to get the data from the 'player' row.

I can't get the player name out using this code.
I've tried a thousand different things.

have a look... I tried to clean up the code or you this time...

Code: Select all

<?php




$host   = 'localhost'; // Hostname of MySQL server
$dbUser = 'xxxx';    // Username for MySQL
$dbPass = 'xxxx';    // Password for user
$dbName = 'drafts'; // Database name

// Make connection to MySQL server
	if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {
 	die('Could not connect to server');
	}

// Select the database
	if (!mysql_select_db($dbName, $dbConn)) {
	 die('Could not select database');
	}

// Define reusable "chunks" of SQL
$table = " FROM {$_GET['league']}";
$where = " WHERE roundnumber='{$_GET['round']}' AND draftyear='{$_GET['year']}'";


// Query to count the rows returned
$sql = "SELECT COUNT(*) as numrows" . $table . $where;

// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);

$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);


$magicmod = $row['numrows']%3;
$magicdiv = $row['numrows']/3;
// A query to fetch the rows
$sql = "SELECT * " . $table . $where;

// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);


// Fetch rows from MySQL one at a time
		 

$numberofpicks = mysql_num_rows($queryResource);
$i = 0;
      while ($row = mysql_fetch_assoc($queryResource) && $i <= $numberofpicks) {
           if ($i % ceil($row['numrows']/3) == 1){
                ?>
               <div style = 'float:left; width:100px;'>";
  <?php
           }    
  ?>
   <span style = 'background-color:#FF4323; margin:2px; width:90px; text-align:center;'>
  <?php echo "Player:". $row['player'] ."";
		echo $numberofpicks;
	?>
    </span><br />
    
    			<?php
   			 if ($i % ceil($row['numrows']/3) == 0){
    			?>
        		</div>
				<?php
   			 }
    		$i++; 
		
		
}
if ($magicmod == 0){
echo "MAGICMOD = 0!";
	 echo $magicdiv;  
   echo $magicmod;
}
if ($magicmod == 1){
echo "MAGICMOD = 1!";

}
if ($magicmod == 2){
echo "MAGICMOD = 2!";

}

mysql_close($dbConn);

?>
I'll leave this test page for you to check.
Here's what I get...
http://24.244.141.135/alpha/sc/draft/dr ... 05&round=3[/url]
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I hate to post again on this topic but.. here goes
I've tried your snippet in my code trying only to get the data from the 'player' row.
i do not understand what you are saying. Just draw out or make an image which will explain how you want the output to be. You want three columns basically but what are things do you want to show in it. If you can make me understand the problem better, I can provide a solution as soon as possible...
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

No worries!

Post by lemonfreshmedia »

THANK YOU SOO MUCH DUDE!

Got it running fine now. Just had to do some playing with it


CHeers, dont hesitate to call on me for any FLash or animation projects you might have.

http://www.lemonfreshmedia.com


You rock!
Post Reply