Hello and welcome to our community! Is this your first visit?
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
    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.
    // 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. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Denver, Colorado USA
    Thanked 506 Times in 494 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 -

    $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
    		case ($fc >= 'a' && $fc <= 'g'):
    			$data[1][] = $row;
    		case ($fc >= 'h' && $fc <= 'n'):
    			$data[2][] = $row;
    		case ($fc >= 'o' && $fc <= 'u'):
    			$data[3][] = $row;
    		case ($fc >= 'v' && $fc <= 'z'):
    			$data[4][] = $row;
    			$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>";
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.


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