List in month, day order

[jehoshua]

I have a table structure as ..

CREATE TABLE `sometablename` (
`ID` bigint(20) UNSIGNED NOT NULL,
`christian_name` varchar(60) NOT NULL DEFAULT '',
`middle_name` varchar(64) NOT NULL DEFAULT '',
`surname` varchar(50) NOT NULL DEFAULT '',
`date_of_birth` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`preferred_name` varchar(64) NOT NULL,
`maiden_name` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and just need a simple list like ..

Year Born Christian name Date

2016 Fred Jan 14

where Fred's date_of_birth = 2016-01-14 00:00:00

Have been testing with SQL in PhpMyAdmin as

SELECT * FROM `sometablename` ORDER BY MONTH(date_of_birth),DAY(date_of_birth)

and the order is okay. However, wanted to use php and provide a nice looking list, as per example above. My php is quite rusty; haven't used it for quite a while.

[jehoshua]

So, this code has a few errors, but it is in general what I need to do

<?php

$con=mysqli_connect($hostname,$username,$pwd, $dbname);
#
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM $table_name ORDER BY MONTH(date_of_birth),DAY(date_of_birth)");

echo "<table border='1'>
<tr>
<th>Year</th>
<th>Firstname</th>
<th>Lastname</th>
<th>DateofBirth</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row[YEAR('date_of_birth')] . "</td>";
echo "<td>" . $row['christian_name'] . "</td>";
echo "<td>" . $row['surname'] . "</td>";
echo "<td>" . $row['date_of_birth'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

Seems the YEAR() is MySQL, not php, as I got

PHP Fatal error: Call to undefined function YEAR()

Also, I tried passing an array on the connect and got

PHP Warning: mysqli_connect() expects parameter 1 to be string, array given in /************.php on line 9

[not2easy]

I don't know much about php coding, I use a few snippets and includes of php to keep things current, so I'm not an authority by any means. Others who do know what they're doing will drop in shortly - but I do know that to show the current year I add this piece:

<?php echo date('Y'); ?>

which looks like php wants to use ('Y') or maybe just Y for the year?



_{just trying to help}

[jehoshua]

Thanks for the tip. Have just about got it all working now. :)

[jehoshua]

It's working okay now. Some of the output table needs padding here and there, but it's only cosmetic changes now.

<?php

$con=mysqli_connect($hostname,$username,$pwd, $dbname); 

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM $table_name ORDER BY MONTH(date_of_birth),DAY(date_of_birth)");

echo "<table border='1'>
<tr>
<th>Year</th>
<th>Name</th>
<th>Date</th>
<th>Parent</th>
</tr>";

while($row = mysqli_fetch_array($result))
{

$datetime = new DateTime($row['date_of_birth']);

if (empty($row['preferred_name'])) {
 $name = $row['christian_name'];
} else {
 $name = $row['preferred_name'];
}

echo "<tr>";
echo "<td>" . $datetime->format('Y') . "</td>";
echo "<td>" . $name . "</td>";
echo "<td>" . $datetime->format('M') . " " . $datetime->format('d') . "</td>";
echo "<td>" . $row['Parent'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

Is it possible to replace this line ..

$con=mysqli_connect($hostname,$username,$pwd, $dbname); 

with the use of an array, instead of variables ?

[Peter_S]

Is it possible to replace this line .. $con=mysqli_connect($hostname,$username,$pwd, $dbname);

What do you mean? To pass an array to the mysqli_connect function, instead of 4 parameters ?

By the way, just to be pernickety, when the MySQL connection fails, you need to exit, or do "something else". Here, if the connection failed, you still run the query and process it. You should also test if the query didn't fail (for example, a delay expiration, the table might be missing, corrupted, etc,...), and verify there is at least one row returned before outputting the results.

And as a bonus, when you use "echo", you can use "," (coma) instead "." (dot). Because when you use the dot, the PHP interpreter will first create a string by concatenating all parts, and then output the string. With the coma, it directly outputs the parts, and avoid the creation and allocation of the temporary string. This is totally negligible at the scale of the whole script and network usage, but I am supporter of "do just what is needed" :)