Hi Guys,

 

I am hopeing someone here (with infinitely more experience in PHP and mySQL) can help me to solve a problem with my website.

 

I am attempting to post 3 news posts on the main page, with the following code:

<div class="news_post">
<a class="heading" href="http://dev.manbeans.com/' . $row['page_path'] . '">' . $row['heading'] . '</a>
<h3>by <span>' . $row['poster'] . '</span> on ' . $row['date'] . '</h3>
<a class="news_image" href="http://dev.manbeans.com/' . $row['page_path'] . '"><img alt="description here" src="/images/' . $row['image_path'] . '" /></a>
<p>' . $row['intro_text'] . '</p>

<a class="read_more" href="http://dev.manbeans.com/' . $row['page_path'] . '">READ MORE</a>
<a class="comment" href="http://dev.manbeans.com/">Comment</a>
</div>

 

I am currently using 4 tables in my MYSQL database:

 

Table Name: news_posts
id (Unique)
poster
date
event_id (P key)
news_id (P key)
image_id (P key)

 

Table Name: events
id (P key)
name
intro_text
page_path

 

Table Name: news
id (P key)
heading
intro_text
page_path

 

Table Name: images
id (P key)
image_path

 

I have only included above, information from each table that is required for the news posts and keys etc.

 

I can currently call the last 3 news posts by news_posts id, however, to get the rest of the information I need to know which table to look for it in, eg. news items, need to use data from the 'news' table and events need data from the 'events' table. This is driving me up the wall.

 

Using my Sitepoint book I just attempted:

 

$sql = "SELECT * FROM news_posts INNER JOIN news ON news.id = news_id INNER JOIN events ON events.id = events_id ORDER BY news_posts.id DESC LIMIT 3";

 

I was attempting to use joins to link all the tables and take out all the data at once, so I could pick through it and use the parts I required. This jumps out at "Error performing query". I have had it working in a more simple format, but that was with duplicate data. I am now attempting to do a proper job and am well out of my depth.

 

I would really appreciate any advice, even just pointing me in the direction of resources you think I would benefit from. If I have no supplied enough information, just let me know and I will get back to you. If you need to see the website, it can be viewed at dev.manbeans.com!

On a side note, next time I am in the pub with my friends and we come up with an amazing idea for a website, I will seriously consider my lack of any experience or ability in making websites and not agree do it.......well perhaps!

On a side note, next time I am in the pub with my friends and we come up with an amazing idea for a website, I will seriously consider my lack of any experience or ability in making websites and not agree do it.......well perhaps!

 

nah keep it up... its how most of us... me included get into the industory in the first place

 

try this SQL, i find it an easier way todo joins.

 

SELECT * FROM news_posts AS np, news AS n, events AS e WHERE n.id = np.news_id AND e.id = np.event_id ORDER BY np.id DESC LIMIT 3

nah keep it up... its how most of us... me included get into the industory in the first place

 

try this SQL, i find it an easier way todo joins.

 

SELECT * FROM news_posts AS np, news AS n, events AS e WHERE n.id = np.news_id AND e.id = np.event_id ORDER BY np.id DESC LIMIT 3

 

Thank you for the encouragement. I replaced with query with yours and still get "Error performing query". I don't understand your way any more than mine, even less infact. Am I going about this the wrong way completely? or am I on the right track?

ok, well that error isnt a native error from php or mysql so, the script its self will be using echo and or die with the message you see... can you post/pm the php... will probbly be somthing simple.

Ah you are correct, I should of posted that anyway, here it is:

 

<?php
$sql = "SELECT * FROM news_posts AS np, news AS n, events AS e WHERE n.id = np.news_id AND e.id = np.event_id ORDER BY np.id DESC LIMIT 3";
$news = @mysql_query($sql);
if (!$news)
exit('<p>Error performing query</p>');						
while($row=mysql_fetch_array($news))
echo '

<div class="news_post">

<a class="heading" href="http://dev.manbeans.com/' . $row['page_path'] . '">' . $row['heading'] . '</a>
<h3>by <span>' . $row['poster'] . '</span> on ' . $row['date'] . '</h3>
<a class="news_image" href="http://dev.manbeans.com/' . $row['page_path'] . '"><img alt="description here" src="/images/' . $row['image_path'] . '" /></a>
<p>' . $row['intro_text'] . '</p>

<a class="read_more" href="http://dev.manbeans.com/' . $row['page_path'] . '">READ MORE</a>
<a class="comment" href="http://dev.manbeans.com/">Comment</a>

</div>';
?>

 

I hope this helps!

Thank you for all your help SniderDK. I have just found an error in my original statement and so your fix. One of the columns was incorrectly named (np.event_id -> np.events_id). I have fixed that so the query no longer gives an error message, however it does (distressingly, hehe) return empty data. When I try the query in the database manager, it shows all the columns I would need, but no data in them at all. I cannot understand why.

 

Another potential problem, is that the two tables (events and news) contain duplicate columns (page_path for instance) which both need yet how will I pick the relevant one if the query returns fields with nothing in? More food for thought. I think perhaps I am barking up the wrong tree, perhaps someone has another suggestion?

Do a quick database export of the tables in question and upload it here. I'm sure it will allow your issue to be solved much faster, as people are second guessing you at the mo

Thank you very much. I had not thought of that, I have attached it below (exported as .sql and then zipped as it would not let me upload .sql) I hope that is correct.

 

(update: exported with create this time)

mb_website.zip

Can you include the create statements aswell - that was the main idea

 

As you're using heidi sql you can just check the 'create' box on the export dialog

I am really sorry I am making it so difficult to help me. I do appreciate all your time. I have changed the uploaded file above with the export using create. I hope it is correct this time. Man I feel out of my depth!

Thanks Rallport i was away gaming.

 

the reason its not showing you any results is because the query says where event id is in the news_post events_id... so the 2 rows without a event id fail there, and the other row has an events_id but no news_id

 

SELECT n.heading, np.poster, n.intro_text, np.date, n.page_path, n.image_path FROM news_posts AS np, news AS n WHERE n.id = np.news_id

 

is the same but here were not selecting the event data or using * asking for the data we need (for example there's no need to load the full text for each entry to just show a summary)... this also means you have more control of what you will call the columns in the returned array using aliases... so where you have 2 page_path's you could use this sql syntax to call page_path something different like news_page_page like so....

 

n.page_path AS news_page_path

## in context
SELECT n.heading, n.page_path AS news_page_path, np.poster, n.intro_text, 

 

 

same apply's to the table aliases.. your just making a new reference to use news_posts AS a_new_name..

 

to use the query to display the full page else where you just need to add the extra fields you want in the first part of the select statement and add np.id = $_GET['id']...

 

you do need to look into making the input data safe to use in the query's, but we can get onto that once you have something nice play with

I needed sleep last night, so took a step back and left it for this morning. Which worked well. As you can see from: dev.manbeans.com it is currently displaying the latest 3 (out of 3!) articles (wether they be news or events).

 

I realised I had fudged the database, by having heading, page_path and page_url twice. Once in events and once in news, which was making me try and workout which I needed each time.

 

Using what you have taught me above and re-arranging my database (new setup attached), I have used this query:

 

<?php
$sql = "SELECT s.heading, s.poster, s.date, s.intro_text, s.page_path, s.image_path FROM shared AS s ORDER BY s.id DESC LIMIT 3";
$news = @mysql_query($sql);
if (!$news)
echo('<p>Error performing query</p>');						
while($row=@mysql_fetch_array($news))
echo '
<div class="news_post">

<a class="heading" href="http://dev.manbeans.com/' . $row['page_path'] . '">' . $row['heading'] . '</a>
<h3>by <span>' . $row['poster'] . '</span> on ' . $row['date'] . '</h3>
<a class="news_image" href="http://dev.manbeans.com/' . $row['page_path'] . '"><img alt="description here" src="/images/' . $row['image_path'] . '" /></a>
<p>' . $row['intro_text'] . '</p>

<a class="read_more" href="http://dev.manbeans.com/' . $row['page_path'] . '">READ MORE</a>
<a class="comment" href="http://dev.manbeans.com/">Comment</a>

</div>';
?>

 

Your suggestion above worked, but as you mentioned only returned the news data, this made me realise that having the same data in news and events was silly, so I renamed news_posts to shared, and now use that to contain any data that both news and events require. This made my query much more straight forward and thanks to what I have learnt from you I now only call the fields I need to.

 

Thank you so much for your patience and taking the time to help me. My lack of knowledge/experience with databases had meant I caused myself real problems for no reason!

mb_website.zip

 

n.page_path AS news_page_path

## in context
SELECT n.heading, n.page_path AS news_page_path, np.poster, n.intro_text, 

 

same apply's to the table aliases.. your just making a new reference to use news_posts AS a_new_name..

 

This is very interesting, is the aliases only useable inside the query (still much easier to follow, thank you so much), or can it be used to reference the data in the php afterwards?

awesome sauce the site is looking much better its good you figured that out, bet you feel like your in shallower waters now. i will take alook at the sql later on for you, there were a few problems with the table indexes but was saving that till after you got past this point.... wouldn't want information overload!

 

and

 

n.page_path AS news_page_path

 

it can be refrenced in the query and it's passed as a key name in the php variable... its quite handy in some situations!

you do need to look into making the input data safe to use in the query's, but we can get onto that once you have something nice play with

My input data is not safe? If it is only ever me inputting data is that ok? Could you point me towards any resources explaining this issue? I had never thought about how data count effect queries!

this first post explains the situation your avoiding, to add to it you can use mysql comments to stop the rest of the query past the attacked point from running

 

http://www.zymic.com/tutorials/php/sanitisation-and-validation-in-php/

 

the function you want to look at is mysql_real_escape_string

 

http://php.net/manual/en/function.mysql-real-escape-string.php

 

im sure that should help you get going

this first post explains the situation your avoiding, to add to it you can use mysql comments to stop the rest of the query past the attacked point from running

 

http://www.zymic.com/tutorials/php/sanitisation-and-validation-in-php/

 

the function you want to look at is mysql_real_escape_string

 

http://php.net/manual/en/function.mysql-real-escape-string.php

 

im sure that should help you get going

Aha, I see. So as I am using /index.php?id=1 so that my events and news pages know if someone wants to view a specific event/news item, I need to be careful, as someone could edit that and cause problems when the page runs the SQL query using 'id'?

 

Crafty little...! I really appreciate the heads up!

 

If I understood the articles you linked correctly, in this case I can simply validate id to make sure it is a number, then apply mysql_real_escape_string() and it will be safe, in this case?

yeah that's basicly it... you just need to know that the data you are using, be it for selecting or inserting the data is exactly what you expect it tobe and nothing else and for complex strings that do contain problem data like quotes its escaped so it wont do any harm...

 

one thing is when displaying escaped data you need to use the function stripslashes this will remove the database escaping so you wont get lots of \ in the page display

yeah that's basicly it... you just need to know that the data you are using, be it for selecting or inserting the data is exactly what you expect it tobe and nothing else and for complex strings that do contain problem data like quotes its escaped so it wont do any harm...

 

one thing is when displaying escaped data you need to use the function stripslashes this will remove the database escaping so you wont get lots of \ in the page display

I think I am following you, hooray! I have a friend visiting from Germany for a few days, but once that is past I will get back to the website and attempt this and then show you the code, if you do not mind.

 

Thank you again for all the help!

i don't mind at all! its a pleasure helping out people who run with the advice and build on it... hope you have a good time over the next few days