Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query data to multiple columns

    Hi guys,

    I want to be able to take data out of the database I have, and display it, into multiple columns, alphabetically.

    I can only figure out how to this myself by exporting ID's 1-10, then 11-20, 21-30 etc and placing each id as an SQL query and echoing it into the page. This does not help me and seems very time consuming.

    Does anyone know how I could query so I could have letters A-G in column 1, H-N in column 2, O-U in column 3, V-Z in column 4, and all numerics in column 5, where the letter or numeric is only based on the first character of the word/business.

    Any help would be great. I'll show you what my SQL looks like at the moment.
    Code:
    <?php
    include('conn_mysql.inc');
    // create database connection
    $conn = dbConnect('query');
    $sql = 'SELECT * 
    FROM hosts 
    WHERE HostID>=1 AND HostID<=4
    ORDER BY HostName ASC ';
    $result = mysql_query($sql) or die(mysql_error());
    $sql2 = 'SELECT * 
    FROM hosts 
    WHERE HostID>=5 AND HostID<=8
    ORDER BY HostName ASC ';
    $result2 = mysql_query($sql2) or die(mysql_error());
    $sql3 = 'SELECT * 
    FROM hosts 
    WHERE HostID>=9 AND HostID<=12
    ORDER BY HostName ASC ';
    $result3 = mysql_query($sql3) or die(mysql_error());
    ?>

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,151
    Thanks
    2
    Thanked 335 Times in 327 Posts
    Your ID's won't have any known relationship to the names. You would need to query for all the data, sorted alphabetical (so that within each column it will be in the order that you want), then pre-process it in your php code, storing it in an array of arrays, one main array for each column based on the range of letters that corresponds to that column, the sub-arrays under each main array holds the actual data.

    To display the data in rows, you would loop, from zero to the highest numerical index of the sub-arrays (the column with the most data in it), and then loop over the 5 main arrays to get the value (if any) for each column.

    An example -

    Code:
    $db = new mysqli('your database credentials'); // using mysqli
    
    
    $query = "SELECT HostName as name FROM hosts ORDER BY HostName";
    $result = $db->query($query);
    
    // define arrays to hold column (1-5) data
    $data[1] = $data[2] = $data[3] = $data[4] = $data[5] = array();
    
    while($row = $result->fetch_assoc()){
    	$fc = strtolower($row['name'][0]); // first character of name
    	switch(true){
    		case ($fc >= 'a' && $fc <= 'g'):
    			$data[1][] = $row;
    		break;
    		case ($fc >= 'h' && $fc <= 'n'):
    			$data[2][] = $row;
    		break;
    		case ($fc >= 'o' && $fc <= 'u'):
    			$data[3][] = $row;
    		break;
    		case ($fc >= 'v' && $fc <= 'z'):
    			$data[4][] = $row;
    		break;
    		default:
    			$data[5][] = $row;
    	}
    }
    
    // find the maximum number rows (the column with the largest number of entries)
    $max = max(count($data[1]),count($data[2]),count($data[3]),count($data[4]),count($data[5]));
    
    echo "<table><tr><th>A-G</th><th>H-N</th><th>O-U</th><th>V-Z</th><th>misc.</th></tr>\n";
    // loop over the rows
    for($x=0;$x < $max;$x++){
    	echo "<tr>";
    	// loop over the columns
    	for($y=1; $y <=5;$y++){
    		$d = isset($data[$y][$x]['name']) ? $data[$y][$x]['name'] : '&nbsp';
    		echo "<td>$d</td>";
    	}
    	echo "</tr>\n";
    }
    echo "</table>";
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •