SRL Archive - Web Development

Im populating a table from MySQL. All this is working so far.

Problem i got is…

I get column names from the columns in the MySQL table and insert them into the table.. but.. i want to only have columns that have data in them not NULL.

Eg..

Name | Age | Checked
John     5          NULL
Ad        7         NULL

So the table should come out as

Name | Age
John     5
Ad        7

Any help?

Show the PHP code you have so far?
Else it's simple… Like this:
[php]
<?php
//Insert MySQL connection

$result = mysql_query("SELECT * FROM `table`");
echo "<table>";
echo "<th>Name</th><th>Age</th>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>". $row["name"] ."</td>";
echo "<td>". $row["age"] ."</td>";
echo "</tr>";
}
echo "</table>";
?>
[/php]

[php]
$q=$_GET["q"];

//Skipped out Connection Info
$result = mysql_query("SELECT * FROM items WHERE ID = '".$q."'");
$row1 = mysql_fetch_array($result);

echo "<table id='items'><tr>";
foreach ( array_keys($row1) AS $header )
{
if ( !is_int($header) )
{
echo "<th>$header</th>";
}
}
echo "</tr>";
$result = mysql_query("SELECT * FROM items WHERE ID = '".$q."'");
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
//moore and more and more
echo "</tr>";
}
mysql_close($dbcnx);
?>[/php]

Can't ye just do:
[php]
<?php
$q = $_GET["q"];

$result = mysql_query("SELECT * FROM `items` WHERE `ID` = '". $q ."'");

echo "<table id=\"items\">";
echo "<th>ID</th><th>Name</th><th>Age</th>";//etc etc
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
//etc etc
echo "</tr>";
}
echo "</table>";

mysql_close($dbcnx);
?>
[/php]

Saves you a lot of hassle.

No because the columns change. I dont want columns to show if the column holds a null value.

mysql_query("SELECT * FROM `items` WHERE `checked` <> 'NULL' ");

Does this work?
[php]
$query = "SHOW COLUMNS FROM tablename";
[/php]

From: Floor66
Does this work?
[php]
$query = "SHOW COLUMNS FROM tablename";
[/php]

Yes but doesnt help.

Colums are retrieved from database and put into a html table. Each time the colums are different because some do not have values.

[php]$get_filled_values = mysql_query("SELECT Name, Age FROM `yourtable_here` WHERE Checked != ''");
while($cur = mysql_fetch_assoc($get_filled_values))
{
echo '
<p>'.$cur['Name'].' ('.$cur['Age'].')</p>';
}[/php]

Is that what your looking for? Or you might try "WHERE Checked != 0"

!= is the same as <>, basicly, though the standard function in mySQL is <>.

Problem is it can be multiple or single columns eg..

WHERE Test1 != 0, Test2 != 0

Etc.. Its all dynamic

I basically have a drop down box, you select the product and its list loads up.

Take this for example.

I choose Chocolate and it comes up with

ID | Name | Fat | Yummy
1     Choc    Yes      Yes

But in the MySQL the data would be..

ID | Name | Fat | Yummy | Healthy | Water
1 Choc Yes Yes NULL NULL

Get what i mean?

From: Dumpin
!= is the same as <>, basicly, though the standard function in mySQL is <>.

Yes it's the same, but your post wasn't posted when I was typing the message.

RogerukI had you on msn already right?

I think this will help you out:
[php]
<?php
$q = $_GET["q"];

$result = mysql_query("SHOW COLUMNS FROM `items`") or die(mysql_error());

$i = 0;
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$a[$i] = $row["Field"];
$i++;
}

for($i = 0; $i < count($a); $i++)
{
$result2 = mysql_query("SELECT `". $a[$i] ."` FROM `items` WHERE `id` = '". $q ."'") or die(mysql_error());
$row2 = mysql_fetch_assoc($result2);
if($row2[$a[$i]] != NULL)
{
echo $row2[$a[$i]];
echo "<br />";
}
}

mysql_close($dbcnx);
?>
[/php]

Just add in the table stuff for yourself. It can be shortened and that shit but this is just more readable imo.

mysql_fetch_assoc() expects parameter 1 to be resource, boolean given

Line 19
[php] $row2 = mysql_fetch_assoc($result);[/php]

Strange… it works for me (I set up a quick table and stuff)
Try renaming the second $result var to $result2

Lol
[php]
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in
[/php]

[php]while($row = mysql_fetch_array($result, MYSQL_ASSOC))[/php]

You've got the ticks wrong, either use ``'s or use none at all. I have no clue why it isn't working for you :p
Also make sure that the table you are selecting from isn't empty

E:
This works for me, see if you get any more special mysql errors:
[php]
<?php
$q = $_GET["q"];

$result = mysql_query("SHOW COLUMNS FROM `items`") or die(mysql_error());

$i = 0;
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$a[$i] = $row["Field"];
$i++;
}

for($i = 0; $i < count($a); $i++)
{
$result2 = mysql_query("SELECT `". $a[$i] ."` FROM `items` WHERE `id` = '". $q ."'") or die(mysql_error());
$row2 = mysql_fetch_assoc($result2);
if($row2[$a[$i]] != NULL)
{
echo $row2[$a[$i]];
echo "<br />";
}
}

mysql_close($dbcnx);
?>
[/php]

I found out problem. Fixed them but now getting error.

[php] for($i = 0; $i < count($a); $i++)
{
echo $a[$i];
$result2 = mysql_query("SELECT '$a[$i]' FROM items WHERE 'id' = $q"); //selects each column one by one
$row2 = mysql_fetch_assoc($result2) or die("Query failed with error: ".mysql_error());
if($row2[$a[$i]] != NULL) //speaks for itself
{
echo $row2[$a[$i]];
echo "<br />";
}
}[/php]

Outputs..

IDQuery failed with error:

So it displays the first column name but then errors :S

you shouldn't use single quotes for them values!! Use these backticks (``) or use nothing.
So '$a[$i]' changes into `" .$a[$i]. "` OR into ". $a[$i] ."

Got it working. Still shows columns that have no values lol.

Update working..

Last help needed.

ID
1
Name
Test
Value 1
3
Value 2
5

Its comming out like that, rather than across the screen…

[php] for($i = 0; $i < count($a); $i++)
{
$result2 = mysql_query("SELECT $a[$i] FROM items WHERE id = $q"); //selects each column one by one
$row2 = mysql_fetch_assoc($result2) or die("Query failed with error: ".mysql_error());
if($row2[$a[$i]] != "") //speaks for itself
{
echo "<th>";
echo $a[$i];//Column
echo "</th></tr>";
echo "<tr><td>";
echo $row2[$a[$i]];
echo "</td>";
}
echo "</tr>";
}[/php]

Well all of your headers should be on the same row of the table but you have a new row (<tr>) every time you output a header.

From: Sex
Well all of your headers should be on the same row of the table but you have a new row (<tr>) every time you output a header.

Yea, cant figure it out for the life of me lol. Been up very long time brain is melting.

//Edit. All done.

Thanks for all the help guys!. Greatly appreciated.

No problem mate :) Happy to help!