Getting data from MySQL
Moderator: General Moderators
-
someguyhere
- Forum Contributor
- Posts: 181
- Joined: Sun Jul 27, 2008 3:24 pm
Getting data from MySQL
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.
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.
Re: Getting data from MySQL
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
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
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
Re: Getting data from MySQL
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 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?
-
someguyhere
- Forum Contributor
- Posts: 181
- Joined: Sun Jul 27, 2008 3:24 pm
Re: Getting data from MySQL
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?
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);
}
?>Re: Getting data from MySQL
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
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
/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
Re: Getting data from MySQL
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
Perfect! Thanks.
It works, and I've managed to trim the code down a bit too:
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
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:
Just after:
But no luck. Below is my current code. Can you point me in the right direction?
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');Code: Select all
if (in_array($_SERVER['REQUEST_URI'], $page_urls))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);
}
?>Re: Getting data from MySQL
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 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.
-
someguyhere
- Forum Contributor
- Posts: 181
- Joined: Sun Jul 27, 2008 3:24 pm
Re: Getting data from MySQL
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
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);
}
?>Re: Getting data from MySQL
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
Yep. There is no test in the path.