PHP/MySQL advice

Ctechinfo · 01-12-2013, 09:31 PM

One of my websites I have built a database the contains support group information. It does work in its rudamentary form but I would like to improve things to better sort by date. The way the database is currently setup I am manually entering the date (i.e. 01/31/13), but I haven't figured out a way to sequentially display only the information based on the current month so that information added to the database for events next year don't affect the list. Before the new year, what I had added to the database showed up like:

01/31/13
08/12/12

Code:

<table cellpadding="5" cellspacing="0" width="100%" align="center" border="1"><tr><th bgcolor="#c2c2c2" text-align="center">Date</th><th bgcolor="#c2c2c2" text-align="center">Location</th><th bgcolor="#c2c2c2" text-align="center">Description</th><th bgcolor="#c2c2c2" text-align="center">Contact Info</th></tr>
<?php
		//Connect to the server and select database; you may need it
		$dbc = mysqli_connect('localhost', 'db_user, 'db_password, 'db_databasename')
		  or die("Could not connect to the database.");
			
	$query = "SELECT * FROM `sgroups` ORDER BY `date` ASC, `city` ASC LIMIT 0, 30 ";
	$data = mysqli_query($dbc, $query);
	

while ($row = mysqli_fetch_array($data)) {
	//Show results table
	echo '<tr><td>' . $row['date'] . '</td><td>' . $row['city'] . ',' .$row['state'] . '</td><td>' . $row['description'] . '</td><td>' . $row['contact'] . '</td></tr>';}
mysqli_close($dbc);
?>
</table>

Also how would I setup the die function to display the "could not connect" message in the table versus ontop of it (ie.

Code:

<table>
<tr><td>' . $row['date'] . '</td><td>' . $row['city'] . ',' .$row['state'] . '</td><td>' . $row['description'] . '</td><td>' . $row['contact'] . '</td></tr>
<tr><td colspan="4">Could not connect to the datase</td></tr>
</table>

versus it spitting out the error message just above the table, then cutting off the rest of the page like:

Code:


Fatal error: Call to undefined function mysqli_connect() in C:\Program Files (x86)\Apache Group\Apache2\htdocs\hydrocephalustalk\index.php on line 65
 <br/>
<tr><td>Date</td><td>City, State</td><td>Descrition</td><td>Conact Information</td></tr>

Thanks

Fou-Lu · 01-12-2013, 09:39 PM

To fix a 'die' call, you simply don't use die. You use conditional checks to determine where you are. Thats a simple matter of check if its false or not.
Your error isn't the same. Your error specifies that you do not have the mysqli available at all. The only way you can capture that is to use either reflection or using function_exists before attempting to call it. Since its fatal, it will immediately terminate the script process at that point.
So when writing it, that would be (this is for when you have mysqli available):

PHP Code:


			
if ($dbc = mysql_connect('...'))
{
    // connection good
}
else
{
    print('Connection failed, do whatever you want with the output here.');
}

Unfortunately for your error, you will need to enable your mysqli library before attempting to use it.

The correct way to actually check the result of the call though would be to check the mysqli_connect_error() function. I believe they've fixed the bug as well when using object oriented mysqli; when it was first released the $connect_error member property would be null.

Ctechinfo · 01-12-2013, 09:55 PM

Yeah on my local computer (my test server) I didn't setup the DB, due being a tad bit lazy and not wanting to keep adjusting the connenction data. But just realized I should move that info to a seperate file so the test server will connect to its own DB and the live server will connect to its own.

Originally I was using XAMMP, thats where I was being lazy about the database. I have since migrated to individual programs (Apache, MySQL, PHP) but haven't installed phpmyadmin yet, but going to do that so I have it.

Ctechinfo · 01-13-2013, 01:11 AM

Installing phpmyadmin has failed..

Quote:

phpMyAdmin - Error

The mysqli extension is missing. Please check your PHP configuration. <a href="Documentation.html#faqmysql" target="documentation"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help" /></a>

in the config.inc.php file in phpmyadmin I added
$cfg['Servers'][$i]['extension'] = 'mysql';

In the php.ini I have both uncommented as well
extension=php_mysql.dll
extension=php_mysqli.dll

I have restarted the server but still getting the error..

Fou-Lu · 01-13-2013, 05:35 AM

Have you followed the documentation here: http://php.ca/manual/en/mysqli.installation.php?
Looks like you're on windows, so you'll need to add the libmysql.dll to a system path if you have < 5.3. 5.3+ won't need the libmysql.dll, so I'd suggest updating the PHP version anyway if you are < 5.3. If not, check the httpd logs, it should be triggering an error if you've uncommented the extensions in PHP but it still won't load.

Ctechinfo · 01-13-2013, 06:36 AM

Quote:

Originally Posted by Fou-Lu

Have you followed the documentation here: http://php.ca/manual/en/mysqli.installation.php?
Looks like you're on windows, so you'll need to add the libmysql.dll to a system path if you have < 5.3. 5.3+ won't need the libmysql.dll, so I'd suggest updating the PHP version anyway if you are < 5.3. If not, check the httpd logs, it should be triggering an error if you've uncommented the extensions in PHP but it still won't load.

Windows 7 Home Premium
Apache 2.0.64 (Newest Windows version I found)
PHP 5.2.17 (to be compatible with live server)
MySQL 5.5.29 (oddly enough the phpinfo()) script doesn't even mention MySQL)..

The installation tutorial(s) I used were all found on the same site (which of coarse I can't recall now).

Ctechinfo · 01-13-2013, 06:41 AM

via the httpd error log:

Quote:

Warning: PHP Startup: Unable to load dynamic library './php_mysql.dll' - The specified module could not be found.
in Unknown on line 0 
PHP Warning: PHP Startup: Unable to load dynamic library './php_mysqli.dll' - The specified module could not be found.\r\n in Unknown on line 0
 
Warning: PHP Startup: Unable to load dynamic library './php_mysqli.dll' - The specified module could not be found.

so evidently even though php code is getting interpeted things are not working.

Fou-Lu · 01-13-2013, 03:33 PM

That's the problem. You need libmysql.dll to be placed in a system path, or even better, create a new system path and add the dll there. Alternatively, get rid of the 5.2 version of PHP and replace it with a 5.3+ version instead. Then you won't need the libmysql.dll library.

Ctechinfo · 01-13-2013, 07:11 PM

Quote:

Originally Posted by Fou-Lu

That's the problem. You need libmysql.dll to be placed in a system path, or even better, create a new system path and add the dll there. Alternatively, get rid of the 5.2 version of PHP and replace it with a 5.3+ version instead. Then you won't need the libmysql.dll library.

I am trying to upgrade to:
Apache 2.4.3
PHP 5.3.20 V9 TS

See to be encountering a lot of no response/not responding issues..

After several attempts with installing Apache 2.4.3 I reverted back to 2.2.21. I seem to havw the bulk of things setup.. phpMyAdmin is complaining about the configuration storage not being completely configured.. And phpinfo was spitting out a few warnings, but at least I am making progress on the setup..

Now what about the date question?

Fou-Lu · 01-13-2013, 09:49 PM

If your dates are showing in the format mm/dd/yy, than these are strings. The best thing to do is convert to the proper datatype by creating a new field and copying the data across, then deleting a column and renaming the created on. You can use the mysql conversion functions for that.
The same function can be used to convert it on the fly: ORDER BY STR_TO_DATE(date, '%m/%d/%y'), which of course will be slow slow.

To upgrade apache and 5.4 PHP, make sure you grab the httpd from the Apache lounge, not apache.org. You'll also need the appropriate handler (php5apache2_4.dll) which I believe is a separate download at the lounge.

Ctechinfo · 01-13-2013, 10:17 PM

Quote:

To upgrade apache and 5.4 PHP, make sure you grab the httpd from the Apache lounge, not apache.org. You'll also need the appropriate handler (php5apache2_4.dll) which I believe is a separate download at the lounge.

I did get the zip from the lounge, although I didnt grab the 2_4.dll yet.. I think I am going to stick with the current setup for a bit instead of blowing things up every other day (other than code that is).

Quote:

If your dates are showing in the format mm/dd/yy, than these are strings. The best thing to do is convert to the proper datatype by creating a new field and copying the data across, then deleting a column and renaming the created on. You can use the mysql conversion functions for that.
The same function can be used to convert it on the fly: ORDER BY STR_TO_DATE(date, '%m/%d/%y'), which of course will be slow slow.

Thanks for the info. The current column is vachar(15) and the dates are entered as mm/dd/yy format.. very poor setup but not bad for a newb... lol

Ctechinfo · 01-14-2013, 01:33 AM

Well it was somewhat successfull. I got the new table setup on my test server. Everything seems to work, BUT the date format I am not pleased with showing yyyy-mm-dd

I used the calander button on the field in phpMyAdmin and is filled in the box with it in yyyy-mm-dd format. I want to have it showing on the webpage as mm/dd/yyyy

Fou-Lu · 01-14-2013, 01:59 AM

That can be done either by using the mysql's DATE_FORMAT function or by using PHP's DateTime class / strtotime and formatting as desired.

Ctechinfo · 01-14-2013, 02:41 AM

had to reload phpMyAdmin and copy the new bd info from the test server and import it to the live server for it to work.. Bad couple days with coding..

Ctechinfo · 01-14-2013, 10:38 PM

Since I am now working from two different databases (1 local, 1 on my live server) I would like to modularize the the connection info. That way the db information is in its own file on each server and the pages with the queries link to the connection file to get the appropriate info..

dbconnect.php

PHP Code:


			
<?php

// db properties

$dbhost = 'localhost';

$dbname = 'test';

$dbuser = 'root';

$dbpass = 'password';

?>

I put in //notes to explain what I tried so far and the outcome.

PHP Code:


			
<?php

include("/includes/dbconnect.php"); // I have tried having the include here and it did not seem to work, through errors



print('<table cellpadding="5" cellspacing="0" width="100%" align="center" border="1"><tr><th bgcolor="#c2c2c2" text-align="center">Date</th><th bgcolor="#c2c2c2" text-align="center">Location</th><th bgcolor="#c2c2c2" text-align="center">Description</th><th bgcolor="#c2c2c2" text-align="center">Contact Info</th></tr>');

        //Connect to the server and select database; you may need it

        // I have also tried the $dbc line in the include instead, and replacing the line with the include, but that did not work either through a 500 page error. $dbc = mysqli_connect('localhost', 'dbuser', 'dbpass', 'dbname')

          or die("Could not connect to the database.");

            

    $query = "SELECT * FROM `sgroups` ORDER BY `date` ASC, `city` ASC LIMIT 0, 30 ";

    $data = mysqli_query($dbc, $query);

    



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

    //Show results table

    echo '<tr><td><center>' . $row['date'] . '</center></td><td><center>' . $row['city'] . ', ' .$row['state'] . '</center></td><td><center>' . $row['description'] . '</center></td><td><center>' . $row['contact'] . '</center></td></tr>';}

mysqli_close($dbc);

print('</table>');

?>