Data displayed in 2 rows if many records

[skipper2]

Hello,

Here is what I am trying to do. I have a database with states and some of the cities in that state. Some states have 2 or 3 entries while others have in excess of 100. What I am attempting to do is create 2 rows if the state has more then 50 records. Preferably checking the total number of records and then determining how many records for each column to make them equal or off by 1 if there is an odd number. I am using PHP with a MySQL DB on Red Hat.

Thanks

[leatherback]

Hi,

have a look at the function [man]mysql_num_rows[/man]

[hollyspringer]

We need some additional information...

Are your retrieving/displaying the data for a single state or more more than a single state?

When you say two rows, do you mean two lines on the screens or two rows in a table?

What separates cities placed on the same row? If you are using a table, as they in separate cells?

[leatherback]

Hi Hollyspringer, Skipper,

I think the information given is enough to give a general answer, but I would like to see some effort before writing longs scripts. I think you should give it a try, and let us know where you get stuck. Just us writing code for you will result in you not understanding how it works, and certainly not being able to come up with a solution for a problem next time.

Remember: We all learned at a certain point. And although especially the first few problems can look horrible, and unsolvable, with trying things you learn most. So give it a try, show us some code, and we go from there, how is that? Anyway.

You might want to use a general query like:
select * from Table where SomeCondition

combine this with distinct (Which will give you unique results) and count() which will give you the number of occurances of that unique result. Then I would use the information from that query to query the table again, and produce output tables. I am sure that if you browse the forum & the MySQL manual you will find that the problem is solvable. If not.. Let us know.

[skipper2]

There would most likely be a table within the PHP coding. I have seen a few scripts online but they haven't worked propperly. I would be displaying every state, but if I can get it for 1 state I can just re-use it for every state's display on the page unless there is a better way. I would be connecting to a datasource which filters by the state and arranges in alphabetical order by organization. I am looking for the coding where you can set it to 2 rows if the number of organizations in a state is over 100 and it would then create a table for the first 50 to be displayed in the first comlumn and then it creates a table or column beside the first one to display the remaining items.

Example:

New Jersey__BloomfieldNewark
_________CaldwellNew Brunswick
___________Denville

Nevada_____Las Vegas
___________Reno

____ indicates spaces for alignment.

That is a simple version of it because like I said I would use it for states with over 100 entries.

Hope that helps.

Thanks

[skipper2]

Hey Leatherback,

You are right of course. I do have some scripting done, I will post it when I get home from work. Basically what it does is it checks the number of items in the dataset, if it is less than 100 it simply prints them down in 1 column, if it is greater then 100 it then checks to see if there is an odd or even number of results then divided that number by 2 and then adds 1, only if there is an odd number, to get the number for the first column and then displays the remaining items in the second column. What is happening is that it is just showing the last 50% in both columns 2x. I will post it when I get home from work so you can see the direction that I went.

Thanks

[sidney]

now i understand i think you are confusing rows and columns

any way to display the way you want you will need to read the data in to an array before displaying otherwise you wont get the alphabetical order going down the page

[leatherback]

Hi Sidney,

I would disagree to that:

If you want each colum to be alphabetical, you could create a class for each colum, one aligned to the left margin, the other to the right side of the first colum.

If you want it like:
a b
c d
e f ...

You could just add the class tag 1 for the first, and the indented for each second value to come out:

<p class="state">
nevada
<span class="LeftAlign">
Bloomfield<br>
Caldwell<br>
</span>
<span class="RightAlign">

Newark<br>
</span>
</p>

or:

<p class="state">
nevada
<span class="LeftAlign">Bloomfield</span>
<span class="RightAlign">Caldwell</span>
<span class="leftAlign">Newark</span>
</p>

I think that should work!?

--edited--
Of course you could use basic table structures to do this ass well, but CSS is nicer, I think.

[hollyspringer]

There are other ways to do this but this way may be easy to follow. The difficulty arises because of the ordering of the cities within the columns.

<?PHP
/*

Given the following:

CREATE TABLE mytable (
state varchar(30) NOT NULL default '',
city varchar(30) NOT NULL default ''
) TYPE=MyISAM;

INSERT INTO mytable VALUES ('New Jersey', 'Bloomfield');
INSERT INTO mytable VALUES ('New Jersey', 'Caldwell');
INSERT INTO mytable VALUES ('New Jersey', 'Denville');
INSERT INTO mytable VALUES ('New Jersey', 'Newark');
INSERT INTO mytable VALUES ('New Jersey', 'New Brunswick');
INSERT INTO mytable VALUES ('Nevada', 'Las Vegas');
INSERT INTO mytable VALUES ('Nevada', 'Reno');
*/

mysql_connect("host","userid","password");
mysql_select_db("database");

// Start table.
echo "<table border=0 cellpadding=2>\n";
// Go through state one at a time.
$result1=mysql_query("SELECT DISTINCT state FROM mytable ORDER BY state");
while ($row1=mysql_fetch_assoc($result1)) {
$state=$row1['state'];
// Get cities for this state and put in an array.
$result2=mysql_query("SELECT city FROM mytable WHERE state='$state'");
while ($row2=mysql_fetch_assoc($result2)) $cities[]=$row2['city'];
// Calculate number of cities in each column.
$incol=min(count($cities), 50); // << change to a number other than 50 if desired.
// Remember index for first city in second column.
$c2=$incol;
// Generate each row of state data.
for ($c1=0; $c1<$incol; $c1++, $c2++) {
// Begin row.
echo "<tr>";
// Display state cell.
if ($c1==0) echo "<td>$state</td>"; else echo "<td>&nbsp;</td>";
// Display value for first city column.
echo "<td>" . $cities[$c1] . "</td>";
// Display value for second city column.
if ($c2<count($cities)) echo "<td>" . $cities[$c2] . "</td>"; else echo "<td>&nbsp;</td>";
// End row.
echo "</tr>\n";
}
// Clear cities table.
unset($cities);
}
// End table.
echo "</table>\n";
?>

[sidney]

another way of doing it this leaves a blank tr between states which is on purpose

 <?php
$numcitys=3; // more than this number of citys and another column is added
require("connect.php");
$result=mysql_query("SELECT * FROM mytable ORDER BY state,city");
while ($row=mysql_fetch_assoc($result)) 
	{
	 $statearray[$row['state']][]=$row['city'];// read all data into array
	}
echo "<table border=\"1\" >\n"; 	
foreach($statearray as $state => $cityarray) //loop through states
	{
	$citycount=count($cityarray); //count number of citys
	echo "<tr><td>".$state."</td>";
	if($citycount>$numcitys) //if we have more than numcitys 
		{
		$citycount=ceil($citycount/2);//half count of citys if two columns
		}
	for($i=0;$i<$citycount;$i++) //start loop through citys
		{
		echo"<td>".$cityarray[$i]."</td>"; //echo city first column
		$j=$i+$citycount; //create marker for second column
		if(isset($cityarray[$j])) //if second column value exsist 
			{
			echo"<td>".$cityarray[$j]."</td>";//echo city second column
			}
//if($i<$citycount-1)	uncomment to remove extra tr between states
		echo"</tr>\n<tr><td></td>"; //start new row for citys		
		}
	echo"</tr>\n"; //end row for states
	}
echo"</table>\n";
 

[skipper2]

Hey guys,

Thanks, I will try them out when I get home from work. Leatherback, I have no problem doing it in the format that yuo showed there, I want it like this though:

Col1_Col2Col3
NJ__A_E
___B_F
___C_G
______D

Mike

[skipper2]

Sidney,

Thank you so much, you are a genius. That is exactly what I was looking for and it works like a charm. I just had to remove the require("connect.php"); and get rid of the table border and add my css and it is wonderful. Thank you so much for your assistance in this matter as well as everyone else that replied with advice.

Mike