PHP - Output Into Multiple Columns...

Hi guys, is there anyway to process this $result from a mysql query inside PHP so that the data below will be formatted to a pivot-like table? The number of rows and columns of the output 'table' will be indefinite. Thanks so much!

Data:
ID   Row   Col   Name
1   1   A   A1
2   2   A   A2
3   3   A   A3
4   1   B   B1
5   2   B   B2
6   3   B   B3
7   1   C   C1
8   2   C   C2
9   3   C   C3


Results:
A1(1)   A2(2)   A3(3)
B1(4)   B2(5)   B3(6)
C1(7)   C2(   C3(9)

I have this code which works fine. I want to improve it by displaying it into multiple columns. Current output:

TITLE 1
JOB1
---------
TITLE 2
JOB 2
---------
ETC...

I want it to display like this instead:

TITLE 1  |   TITLE 2
JOB 1     |  JOB 2

Here is my code:

$sql = "SELECT * FROM coaches WHERE position = 'Junior Coach'"; 
$result = mysql_query ($sql); 

while ($row = mysql_fetch_array($result)){ 
$id = $row["id"];
$firstname = $row["firstname"];
$lastname = $row["lastname"];
$position = $row["position"];
$image = $row["image"];
?>
             <table width="183" border="1">
                <tr>
                  <td align="center" height="18">
                  <?php echo "<img id='image_shadowCoaches' src=" . $image . ">"; ?><br />
  <?php echo "<div id='Coaches_Detail'>".$firstname." ".$lastname."</div>" ?>
                 <?php echo "<div id='Coaches_DetailLower'>".$position."</div>" ?>
                  
                  </td>
                </tr>
                </table>
             <?php } ?>

Hi there,

This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself.

1. Pull all the records from my database.
2. Assign their unique user id's, callsigns and destinations.
3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'.

For example if three people are going to Heathrow, then it would return 3 results.
4. Then do a mysql_num_rows of the result.
5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier.

All in a loop of course.


// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){

// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_destinations = $atc_arrivals_row['planned_destaiport'];
$atc_callysign = $atc_arrivals_row['callsign'];

// Searches the database and selects all records where the callsign is LIKE the destination airport.
$atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'");

// Counts previous query results.
$atc_arrivals_count = mysql_num_rows($atc_arrivals_check);

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}


Concerned columns are these:
"cid" - Unique client id.
"atcarrivals" - Number of arrivals from count. (This is the problem.)
"planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow.

Thanks.

I have some code that selects one column from mysql table. However when I try to add more it doesn't work.   First the working code....

$query = "SELECT `profile_privacy`  FROM `users` WHERE `user_username` = '$username'";
$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$profile_privacy = mysql_result($result, 0);

And here is where I have added another column....

$query = "SELECT `profile_privacy`, `profile_pictures`  FROM `users` WHERE `user_username` = '$username'";
$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$profile_privacy = mysql_result($result, 0);
$profile_pictures = mysql_result($result, 1);

What have I done wrong?

Many thanks,

Hi

I'm trying to find the max value in 3 columns, new_date, reply_date and finalized_date in my table:

Code: [Select]
$test = safe_query("SELECT MAX(new_date) FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges UNION SELECT reply_date FROM ".PREFIX."cup_challenges UNION SELECT finalized_date FROM ".PREFIX."cup_challenges) AS maxval");
Did some research and have no idea what is wrong/if the above is correct?
The page breaks below this code.

I am loading notifications from a database table called "notifications" and I am having a little trouble getting them to order in the correct way.

my query I'm using right now:

Code: [Select]
$query = mysql_query("SELECT B.* FROM (SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY A.id ASC ) AS B ORDER BY B.state ASC LIMIT 7");

this works well as far as showing the unread notifications on top, then the read notifications below, however it's not ordering the two sets by ID (which the id auto increments so the higher id number is the newest) from newest on top to the oldest on bottom, still keeping them separated by the unread on top, read on bottom (column name is state for showing whether they're read or not).
The order the notifications are displaying by their ID is:

3
5
2
4
1

when it should be:

5
3
2
4
1

I made a small editing system for my news page, and I need to update three columns within my table "announcements" in the database. I tried a method of updating all of them with one MySQL query instead of using three as it just isn't neat. I've searched several methods via google and I've tried all of them, but just can't seem to get it to work. Is this MySQL query correct?


mysql_query("UPDATE announcements SET title = {$title} WHERE id = '$id', content = {$content} WHERE id = '$id', lastmodified = ". date('M-d-Y') ." WHERE id = '$id'");

Not sure if this is the right forum, but I have a database that I will need to populate with a large number of rows (2000+).

I have written a PHP script that uploads individual entries.

Is it possible to use something like a spreadsheet where I can set out the rows/columns as they will appear in the database, and then upload in one go rather than uploading each row individually?

Thanks for any observations and/or help.

Hi, I need help to check multiple rows for many columns, so that the script won't re-insert a duplicate entry. For eg:


$isRowExist=mysql_query("SELECT (field1,field2) from table1 WHERE field1='$f1' AND field2='$f2' ");

if(mysql_num_rows($isRowExist)==1)
   print "already in table1!";
else
   //proceed to insert into table table1


*BUT I tried this and it says mysql_num_rows expects resource, and this warning only shows when I have multipel columns I want to compare (it works just fine with one column in the WHERE clause above)

Please help!

Im having problems with this code in my search button script. I get the following error:  Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Here's the code I use on my search form.

Code: [Select]
<form method="post" action="srch_all.php"><input type=text name='search' size=15 maxlength=255><br><input type=submit></form>

and here's the code I'm using to perform the search query:

Code: [Select]
if ($search)   // perform search only if a string was entered.
   {
    mysql_connect($host, $user, $pass) or die ("Problem connecting to Database");

    $srch="%".mysql_real_escape_string($search)."%";

    $query = "select * from database WHERE column1 LIKE '$srch' or column2 LIKE '$srch' or column3 LIKE '$srch' or column4 LIKE '$srch' ORDER BY column1, column2 DESC, column3 ASC";

 $result = mysql_db_query("database_name", $query);
    if(mysql_num_rows($result)==0) {
        print "<h2>Your search returned 0 Results</h2>";
   }

    else if  ($result)
    {

<restult data stuff here>..

 Cheers

I have a search set up to search a table for the text entered in a textbox, I have two columns in the table, one with the first name of people, and the second with their last names, I am wondering how I can search both, so for instance:

I type in the search field: Roger Smith

in the database it would look like:

First_name-----|-----Last_name
-------------------|-------------------
Roger------------|-------Smith

my current query is:

Code: [Select]
$query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' OR lname LIKE '%$find%'");
But if I type both parts of the name it doesn't return anything. works fine if I just search for "Roger" OR "Smith".

Hi,   How can I select multiple columns from two tables and run a search through multiple fields?   My tables a   t_persons (holds information about persons) t_incidents (holds foreign keys from other tables including t_persons table)   What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors:

$query = "SELECT
p.PersonID
,p.ImagePath
,p.FamilyName
,p.FirstName
,p.OtherNames
,p.Gender
,p.CountryID
,i.IncidentDate
,i.KeywordID
,i.IncidentCountryID
,i.StatusID

FROM t_incidents AS i
    LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID
WHERE
    FamilyName LIKE '%" . $likes . "%' AND
    FirstName LIKE '%" . $likes . "%' AND
    OtherNames LIKE '%" . $likes . "%' AND
    Gender LIKE '%" . $likes . "%' AND
    IncidentDate LIKE '%" . $likes . "%' AND
    KeywordID LIKE '%" . $likes . "%' AND
    IncidentCountryID LIKE '%" . $likes . "%' AND
    StatusID LIKE '%" . $likes . "%'
ORDER BY PersonID DESC $pages->limit";

Errors a

Column 'IncidentDate' in where clause is ambiguous
Column 'KeywordID' in where clause is ambiguous
Column 'IncidentCountryID' in where clause is ambiguous
Column 'StatusID' in where clause is ambiguous

These columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps.   I will appreciate any better way to do this.   Thanx.   Joseph Attached Files  Model - 3.png   76.6KB   0 downloads

Hi There,

I have an SQL query that returns 10 rows, which I want to echo over 2 columns and 5 rows, however, I want rows 1-5 on the left hand column and rows 6-10 on the right hand side.

Is there an easy way to do this? Normally I would do a fetch_array but, that would place the rows in order of, left, right, left, right - if that makes sense?

Table is a standard table with 2 columns and 5 rows.

Thanks
Matt

right now it displays the first column of the array as a header for each foreach loop then i've got the data displayed under each header and it has 4 columns or a number of my choosing with data displayed under the Table Headings. I am trying to make it display 4 or so columns but with the each group having a certain number of grouped table (data/header) in each column. a different amount for each column that i can choose

IF Possible i'd like the choice of being able to remove the headers eg. Z when there is no data in them.

This is an example of how it is being displayed right now

Any Help would be greatly appreciated

Quote

A
Airlie Beach   Andergrove   Alexandra   Armstrong Beach
Alligator Creek
B
Bucasia   Blacks Beach   Beaconsfield   Bakers Creek
Balberra   Bloomsbury   Breadalbane   Ball Bay
Belmunda
C
Cannonvale   Calen   Crystal Brook   Cremorne
Chelona   Campwin Beach   Cape Hillsborough
Conway


Heres The Code i have so far.

Code: [Select]
<?php
  $file = "widget.csv";
@$fp = fopen($file, "r") or die("Could not open file for reading");
$outputArray = array();
$count = 0;

while(($shop = fgetcsv($fp, 1000, ",")) !== FALSE)
{
    $outputArray[array_shift($shop)] =  array_filter($shop);
}

echo "<table>";
foreach ($outputArray as $alphabetical=>$value){
    echo "<th colspan='4'><b>" . $alphabetical ."</b></th>";
    echo "<tr>";
foreach ($value as $key){
$afterkey=preg_replace('/\s+/','-',$key);
    if ($count == 4){
    echo '</tr><tr><td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    $count = 0;
    }else{
    echo '<td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    }
$count++;    
}
    echo "</tr>";
$count = 0;
}
    echo "</table>";

    echo "\n<br />";

?>


Heres an example of what i am trying to do.

A D K N S AlexandraHeadlands DickyBeach KingsBeach Nambour SandstonePoint Aroona Diddillibah KielMountain Ninderry ShellyBeach Doonan KundaPark NoosaHeads SippyDowns B Dulong Kuluin Ningi SunriseBeach Beachmere DeceptionBay Kilcoy NorthArm SunshineBeach BanksiaBeach Noosaville Scarborough Beerburrum E L Beerwah EerwahVale Landsborough O T Bellara Elimbah Tanawha Bellmere Eudlo M P TowenMountain Birtinya Eumundi Maleny Petrie Tewantin Bongaree Mapleton Palmview TwinWaters Bokarina F Marcoola Palmwoods BribieIslandArea Flaxton MarcusBeach Parklands U Buddina ForestGlen MaroochyRiver Parrearra UpperCaboolture Burnside Maroochydore PeregianBeach Buderim G Minyama Pinbarren V Burpengary GlassHouseMountains MoffatBeach PointArkwright Valdora BliBli Mons PelicanWaters H Montville PacificParadise W C Highworth Mooloolaba WeybaDowns CoolumBeach Hunchy Mooloolah Q Warana Caboolture MountainCreek WestWoombye CabooltureSouth I MountCoolum R Woombye Caloundra ImageFlat Morayfield Rosemount Woorim CastawaysBeach Mudjimba Redcliffe WamuranBasin Chevallum J Woodford CoesCreek WoodyPoint Cooroy Wamuran Currimundi Wurtulla X Y YandinaCreek Yandina Z

Well I have a script file that was originally written like this:


Code: [Select]
$query = "UPDATE ".$prefix."users SET nickname='".$nickname."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET gender='".$gender."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET color='".$color."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET profile='".$profile."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET favpet='".$favpet."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET about='".$about."' WHERE username='".$loggedinname."'";
mysql_query($query);

I tried to simplify it by rewriting the following codes below, but unfortunately it did not work. It wouldnt give any errors, but the columns aint updated at all:


Code: [Select]
mysql_query("UPDATE ".$prefix."users SET nickname='".$nickname."' ,
                                      gender='".$gender."' ,
      color='".$color."' ,
      profile='".$profile."' ,
      favpet='".$favpet."'  ,
      about='".$about."' , WHERE username='".$loggedinname."'");

Did I make any mistake rewriting the codes? Or is it actually impossible to update six columns using only one mysql_query? Please help...