Getting data from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Getting data from MySQL

Post by someguyhere »

I tried searching but couldn't come find an answer to this...

Let's say I have a content management type of application, and when someone visits a particular URL, I want it to query the DB to see if the URL exists. If it does, I want it to load the data from the table into an array so that I can populate the page (title tag, h1, content, etc.). I'll obviously be using an if/else statement here

While I'm trying to get it working, the structure of the DB is as follows: the name is agen, there is one table named agen_content and there are two rows in that table, with three fields each:content_url, content_title, content_body.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting data from MySQL

Post by Celauran »

You'll need some manner of page controller to which you redirect all traffic so no matter what URL they type, they end up on the same page. You could then use $_SERVER['REQUEST_URI'] to see which page they requested, check if it exists, and fetch the appropriate data if it does.
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

Ok, so as I have the DB set up now, there is a table for pages, and one of the fields is url (example value would be contact.htm), so if I use an if/then statement along with your suggestion, that *should* do the trick?

Since I don't have a lot of experience w/ php, I just want to make sure I'm heading in the right direction before I take a long trip down the wrong road due to my assumptions ;)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting data from MySQL

Post by Celauran »

someguyhere wrote:Ok, so as I have the DB set up now, there is a table for pages, and one of the fields is url (example value would be contact.htm), so if I use an if/then statement along with your suggestion, that *should* do the trick?
It should be enough to get you started. If the page exists in the database, create the page, if not, redirect to some default page or to an error page.
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

I seem to be getting nowhere with it...

The field that holds the urls has two urls in it at the moment. My challenge right now is just to get the script to tell me whether it finds the url in the database (I'm assuming if it does, my next step is to pass headers to the browser). Any idea what I'm doing wrong here?

Code: Select all

<?php

$mysqli = new mysqli("localhost", "user", "pw", "db");

if (mysqli_connect_errno()) {
	printf("Connection failed: %s\n", mysqli_connect_error());
	exit();
} else {

	$req_url = $_SERVER['REQUEST_URI']; 

	echo $req_url;

	$sql = "SELECT * FROM ag_pages";
	$res = mysqli_query($mysqli, $sql);

	if ($res) {
		while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
			$page_url = $newArray['page_url'];

				if (in_array($req_url, $page_url)) {
					echo "Page Exists";
				}

			}

		} else {

			printf ("Could not retrieve records: %s\n", mysqli_error($mysqli));
		}

	mysqli_free_result($res);
	mysqli_close($mysqli);
}
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting data from MySQL

Post by Celauran »

At a glance it looks good. What is it doing that it shouldn't be? What isn't it doing that it should be?
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

This is the output I get:

/AREA-52/AG/
Warning: in_array() [function.in-array]: Wrong datatype for second argument in /home1/wfir1/public_html/AREA-52/AG/index.php on line 21

Warning: in_array() [function.in-array]: Wrong datatype for second argument in /home1/wfir1/public_html/AREA-52/AG/index.php on line 21
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting data from MySQL

Post by Celauran »

It expects an array and you're probably passing it a string. You can use var_dump to confirm. I'd probably change the while loop to solely populate your $page_url array, then perform the comparison.
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

Perfect! Thanks.

It works, and I've managed to trim the code down a bit too:

Code: Select all

<?php

$mysqli = new mysqli("localhost", "user", "pw", "db");

if (mysqli_connect_errno()) {
	printf("Connection failed: %s\n", mysqli_connect_error());
	exit();
} else {

	$req_url = $_SERVER['REQUEST_URI']; 

	$query = "SELECT * FROM ag_pages";
	$results = mysqli_query($mysqli, $query) or die(mysqli_error());

		while($row = mysqli_fetch_array($results)){
			echo $row['page_url'];
			echo "<br />";
		}

	mysqli_free_result($results);
	mysqli_close($mysqli);
}
?>
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

Ok, now that part works but I can't figure out where to go/what to search for from here.

Let's say that I navigate to a particular page that does exist in the DB; the problem I'm running into now is that I'm getting a 404 error. I've tried sending a header w/ a 200 status, but it doesn't seem to work. I had put:

Code: Select all

header('HTTP/1.1 200 OK');
Just after:

Code: Select all

if (in_array($_SERVER['REQUEST_URI'], $page_urls))
But no luck. Below is my current code. Can you point me in the right direction?

Code: Select all

<?php

if (mysqli_connect_errno()) {
	printf("Connection failed: %s\n", mysqli_connect_error());
	exit();
} else {

	$req_url = $_SERVER['REQUEST_URI']; 

	$query = "SELECT * FROM ag_pages";
	$results = mysqli_query($mysqli, $query) or die(mysqli_error());

		$page_urls = array();
		while($row = mysqli_fetch_array($results)){
			$page_urls[] = $row['page_url'];
		}

	if (in_array($_SERVER['REQUEST_URI'], $page_urls))
		echo "Page Exists";
	else
		echo "No Dice";

	mysqli_free_result($results);
	mysqli_close($mysqli);
}
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting data from MySQL

Post by Celauran »

someguyhere wrote:Let's say that I navigate to a particular page that does exist in the DB; the problem I'm running into now is that I'm getting a 404 error. I've tried sending a header w/ a 200 status, but it doesn't seem to work.
I'm not sure why you'd use headers here. If the page exists in the database, why not just fetch it and display it?
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

Because while testing, I was testing little things, in this case, if the page existed, I was just trying to get it to echo that the page existed first, then when that worked, I was planning to replace the echo w/ the functionality to fetch/display the page. Did I have the wrong idea?
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

Below is the code I tried. If I go to the root, it shows "success" which it should, because the root URL is in the DB. But if I go to another URL that also is in the DB (for example, new-url.html) it still gives me a 404.

Code: Select all

<?php

$mysqli = new mysqli("localhost", "user", "pw", "db");

if (mysqli_connect_errno()) {
	printf("Connection failed: %s\n", mysqli_connect_error());
	exit();
} else {

	$req_url = $_SERVER['REQUEST_URI']; 

	$query = "SELECT * FROM ag_pages";
	$results = mysqli_query($mysqli, $query) or die(mysqli_error());

		$page_urls = array();
		while($row = mysqli_fetch_array($results)){
			$page_urls[] = $row['page_url'];
		}

	if (in_array($_SERVER['REQUEST_URI'], $page_urls))
		
		echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title></head><body>Success!</body></html>';

	else
		echo "No Dice <br />";

	mysqli_free_result($results);
	mysqli_close($mysqli);
}
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting data from MySQL

Post by Celauran »

Have you echoed the $_SERVER['REQUEST_URI']? If you're testing on myproject.com/test/new-url.htm, then you'll likely have to strip /test/ from the URL before using it in your query.
someguyhere
Forum Contributor
Posts: 181
Joined: Sun Jul 27, 2008 3:24 pm

Re: Getting data from MySQL

Post by someguyhere »

Yep. There is no test in the path.
Post Reply