MySQL lists generated in PHP

EricOnAdventure · June 14, 2016

I am using PHP to generate and echo a list form mySQl database. If I get the result "red", I would like to turn it red. Would I just be able to use str replace to replace the text with HTML coding?
Or is there a better way?

requinix · June 14, 2016

What? What result? Turn what red? Replace what text with HTML?

EricOnAdventure · June 14, 2016

Well my SQL is outputting a list for me, lets say they are colors. If I want "red" to be output as not only the word red, but also the color red, how should I do that. I tried with Str replace, but and changing the mySQL, in both cases I just got <font color="red">This is some text!</font> - the html didn't show but became like text. Any Idea?

requinix · June 14, 2016

Okay, that makes things a little bit clearer, but...

What's your code?

QuickOldCar · June 14, 2016

Font has been deprecated a long time, use css style or style right in the html.

I would suggest using rgb or hex versus normal colors so can do them all.

I don't know how simple or advanced your application will be.

If you need functions to use and convert to different color display formats let me know.

Here is a simple example using a function to change the colors.

<?php
function showColor($color){
	if(!isset($color) || empty($color)){
		$color = "black";
	}
	
	return "<p style='color:$color';>$color</p>";
}

//dummy data
$colorarray = array("red","orange","yellow","green","blue","indigo","violet","not-a-color"," ");
?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Show color</title>
</head>

<body>
<div>
<?php
foreach($colorarray as $thecolor){
	echo showColor($thecolor);
}
?>
</div>
</body>

</html>

EricOnAdventure · June 15, 2016

Thanks Quick Old Car, but I still have a small problem. I took what you gave me, tested it on the array and it worked perfectly. Then I tried to apply everything to my database and that is where I am getting an issue.

The only problem seems to be "Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\efun\unisearch\test.php on line 53"

How am I doing it wrong?

Thanks!

<?php
session_start(); 
include("config.php");
function showColor($number){
	if($number == 1) {
		$color = "red";
		$word = "Low";
	}
		if($number == 2) {
		$color = "green";
		$word = "Midium";
	}
		if($number == 3) {
		$color = "blue";
		$word = "High";
	}
			if($number == 4) {
		$color = "orange";
		$word = "Very High";
	}
		if($number == 5) {
		$color = "violet";
		$word = "Extreamly High!";
	}
	return "<p style='color:$color';>$word</p>";
}


	$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE id>0 LIMIT 10";
	
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
	while ($row = mysql_fetch_assoc($sql_result)) {
		
	$Scholerships=$row["Scholerships"];
	
	


?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Show color</title>
</head>

<body>
	<td width="191" bgcolor="#CCCCCC"><strong>Scholerships color</strong></td>
		<td width="191" bgcolor="#CCCCCC"><strong>Scholerships no color</strong></td>
<div>
<td><?php foreach($Scholerships as $thecolor)
	{echo showColor($thecolor);}?></td>
	<td><?php echo $row["Scholerships"]; ?></td>
</div>

</body>

</html>
<?php
}}else
{echo 'fail';} 
?>

QuickOldCar · June 15, 2016

You are wrapping the entire html in the while loop. $Scholerships is not an array.

Can make it an array and use it later like this.

<?php
session_start();
include("config.php");
function showColor($number)
{
    if ($number == 1) {
        $color = "red";
        $word  = "Low";
    }
    if ($number == 2) {
        $color = "green";
        $word  = "Midium";
    }
    if ($number == 3) {
        $color = "blue";
        $word  = "High";
    }
    if ($number == 4) {
        $color = "orange";
        $word  = "Very High";
    }
    if ($number == 5) {
        $color = "violet";
        $word  = "Extreamly High!";
    }
    return "<td style='color:$color';>$word</td>";
}


$sql = "SELECT * FROM " . $SETTINGS["data_table"] . " WHERE id>0 LIMIT 10";

$sql_result = mysql_query($sql, $connection) or die('request "Could not execute SQL query" ' . $sql);

$Scholerships = array();

if (mysql_num_rows($sql_result) > 0) {
    while ($row = mysql_fetch_assoc($sql_result)) {
        
        $Scholerships[] = $row["Scholerships"];
        
    }
    
} else {
    
    $Scholerships[] = "fail";
    
}

?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Show color</title>
</head>

<body>
<div>
<table style="width:191px;">
  <tr>
<?php
foreach ($Scholerships as $thecolor) {
    echo showColor($thecolor);
    
}

?>

  </tr>
</table>
</div>

</body>

</html>

If you want to do directly in the while loop break in and out of php and html just in the <body> section. Use the $row data.

Should look into using css for styling it all.

mysql_ functions are deprecated, should be using mysqli_ or pdo with prepared statements.

Edited June 15, 2016 by QuickOldCar

QuickOldCar · June 15, 2016

Almost forgot...

scholarship, medium, extremely

Another thing is when making functions is to handle if something is empty or not data you expect, such as not being a number.

EricOnAdventure · June 15, 2016

Thanks again, for the help with the code and the spelling

Right now everything that will be changed is a number.

As my mySQL being deprecated - my plan is to get this to work, then go back and change it to mysqli, which I am still unfamiliar with.

While this works as is I ran into a tiny issue.

I want to display the scholarships as a row. When I pulled the data from while ($row = mysql_fetch_assoc($sql_result)) {

they were in a format that worked well for inserting them into columns, now as an array I don't know how to inset them into a column in a working way. Any idea?

Thanks

Muddy_Funster · June 15, 2016

You would be better starting a new thread for this, but basically you're just using the $Scholerships array instead of the $row array - so for example, assuming you have column names "fistName, lastName, enroleDate, colorCode" you can do the following:

foreach($Scholership as $record){
$tableRow = <<<TABLE_ROW
<tr style="color:{$record['colorCode']}"><td>{$record['firstName']}</td><td>{$record['lastName']}</td><td>{$record['enroleDate']}</td></tr>
TABLE_ROW;
echo $tableRow;
}

This obviously doesn't connect with the code that @QuickOldCar has already provided and isn't meant as anything more than an example of how you can handle the information in the array.

Also - I would like to take this chance to discourage you from using SELECT * for queries, name each column that you are retrieving from the table.

EricOnAdventure · June 15, 2016

Also - I would like to take this chance to discourage you from using SELECT * for queries, name each column that you are retrieving from the table.

Muddy, Could you tell me why? Also thanks for the code and suggestions. I'll look into it.

Muddy_Funster · June 15, 2016

Using SELECT * has a couple of issues - not as many as it used to, but it's still generally considered bad practice. When you use SELECT * relinquish a certain amount of control over the dataset that you get back from the server. This means that you always values for every column returned from the query. This means that - barring very limited exceptions - you are creating a result set that contains columns that you at best don't need and at worst can create a vulnerability in your process. There is rarely a need to include the UUID of a record in a reporting query, and I argue that there is never a use case for returning the contents of a password column. So SELECT * = inefficient for the vast majority of queries.

This inefficiency grows any time there is a alteration to the table that increases the number of columns in the table. While functionality evolves and can cause core back-end data tables to require additional columns in order to facilitate new functionality, it is just as common that the initial functionality will still be used as is, but now the queries are pulling back even more redundant data. As well as this, when you start to build more complex queries you will find that using SELECT * is not only inefficient, but that it is counter intuitive to writing queries that are understandable.

When you select the column names specifically you are able to apply aliases to them: this gives the potential to make the whole query more understandable. Another reason is readability. If you go back to code that you wrote 8 months or a year ago and you have listed all the columns that are being taken then you can work out rather quickly what you were doing with that query back when you wrote it.

Then there's the fact that it makes it easier for others to read your code as well: large projects call for multiple pairs of hands or else by the time they are complete the program is either redundant or in need of immediate upgrade; sometimes you need to ask for help, and if the columns are listed in the query those looking that the query can identify better what they are dealing with. Not using SELECT * also makes consistency across your code much easier. If you use a specific naming convention in your scripting you can apply aliases to the column names that conform with this convention when you select each of them rather than having to count on the column names themselves matching - meaning that you can keep your actual column names relevant to the database, and the selected column names relevant to the script.

One of the biggest issues with SELECT * used to be that it basically ignored indexed columns altogether, which was a huge performance issue - I'm pretty sure this was fixed a couple of years ago but, as I haven't used SELECT * on a table in a long, long while I can't say for sure.

There are two occasions where using SELECT * is more or less acceptable :

1) Debugging a tables dataset and

2) When querying a View rather than a Table.

EricOnAdventure · June 16, 2016

You would be better starting a new thread for this, but basically you're just using the $Scholerships array instead of the $row array - so for example, assuming you have column names "fistName, lastName, enroleDate, colorCode" you can do the following:
foreach($Scholership as $record){
$tableRow = <<<TABLE_ROW
<tr style="color:{$record['colorCode']}"><td>{$record['firstName']}</td><td>{$record['lastName']}</td><td>{$record['enroleDate']}</td></tr>
TABLE_ROW;
echo $tableRow;
}
This obviously doesn't connect with the code that @QuickOldCar has already provided and isn't meant as anything more than an example of how you can handle the information in the array.

Also - I would like to take this chance to discourage you from using SELECT * for queries, name each column that you are retrieving from the table.

Muddy I tried to use your advice, but I couldn't get it to work. I print r the Array, and my results were :

Array ( [0] => 3 ) Array ( [0] => 3 [1] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 [4] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 [4] => 3 [5] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 [4] => 3 [5] => 3 [6] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 [4] => 3 [5] => 3 [6] => 3 [7] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 [4] => 3 [5] => 3 [6] => 3 [7] => 3 [8] => 3 ) Array ( [0] => 3 [1] => 3 [2] => 3 [3] => 3 [4] => 3 [5] => 3 [6] => 3 [7] => 3 [8] => 3 [9] => 3 )

Based on this is there any advice you can give. On the bright side I did fix every other problem with this database search script that I ran into.

EricOnAdventure · June 16, 2016

I'll make a new post as this one is getting long and has changed topic a bit

Zane · June 16, 2016

From the looks of your print_r output, I'm guessing that you placed the print_r inside your loop, yes?

That would indeed yield results like that.

Sign In

MySQL lists generated in PHP

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information