PHP question

[catacon]

I was wondering if it was possible to display the table names of a database easily, not the data in the tables, but the table names. For example, if you have the database -my_db- with the table -my_table- and -my_table2-, can you display a link to -my_table- and -my_table2- which will bring you to the respective table. Like in a forum, where you have all the topics listed, but not the posts in the topic, how do you do that?

[9600baud]

Thats not really a PHP question but more of a db question. If you're using MySQL "show tables" should work just fine. Then when you've the table names you can just use "desc your_table_name" to get all the fields. Although you dont need to do that since you can just do a "select * from your_table_name" and use mysql_field_name or odbc_field_name if you're using odbc as the name implies.

[mairving]

Database tablenames are somewhat meaningless to the average joe. There is no reason to display the tablenames. If you wanted to show only the category a simple way would be to create a category table. You could have categories and sub-categories. Running a SQL query with sub-queries would spit out the list of categories and sub-categories. The posts would have a field in them something like category_id. So when someone clicks on Programming and Web Development, it would do a query on all posts that had that category id. Of course, there would be other queries as well. I have worked on some sites were there were a couple of hundred tables. No reason to show them.

[catacon]

Ok, mairving, I understand where you are going with that, but how would you collect all the post with that certain category_id? What would the query look like to do that?

[mairving]

Originally Posted by catacon Ok, mairving, I understand where you are going with that, but how would you collect all the post with that certain category_id? What would the query look like to do that?

A basic setup would look something like this:

(table)category
category_id int(11) auto_increment primary key,
category_name varchar(255),
category_desc text,
parent_id int(2)

(table)posts
post_id int(11) auto_increment primary key,
post_name varchar(255),
post_text text,
category_id int(11)

Basic SQL would be something like:
SELECT category_id, category_name from category
ORDER BY category_name;

This would spit out the categories.

SELECT * from posts WHERE category_id='$category_id';

would give you the posts. for that category.

Some more advanced SQL would allow you to spit out the tables using the parent_id. Say one parent category is General and that category_id=1, then all of the categories with that parent would show underneath that category.

Pretty basic explanation and leaves out a lot but it should get the idea across.

[catacon]

Sweet, thanks. I will mess around with this and see what happens.

Just so I don't have to make a new thread, I will post another question here.
I was trying to store a number made from the rand() function in a database when a user press a button, and then print it out, but I always got the error message "Column count does not match value at row 1" or something like that, and I was wondering what that meant and how I could fix it.

[mairving]

Originally Posted by catacon Sweet, thanks. I will mess around with this and see what happens. Just so I don't have to make a new thread, I will post another question here. I was trying to store a number made from the rand() function in a database when a user press a button, and then print it out, but I always got the error message "Column count does not match value at row 1" or something like that, and I was wondering what that meant and how I could fix it.

That is a sql error message telling you that the count of what you are inserting into the database doesn't match the values on your form.

For instance, in your table you might have these columns:
column1, column2, column3, column4.

Your sql might be something like this:
INSERT into tablename values('$value1', '$value2', '$value3', '$value4');

Works fine as long as you insert data into all of the columns. If instead you do something like this:
INSERT into tablename values('$value1', '$value2', '$value3');
You get the count mismatch error since you have 4 columns and only 3 inserted. It has no idea which goes with what column.

Proper way to do it is to specify the columns that you are using.
INSERT into tablename
(column1, column2, column3)
values('$value1', '$value2', '$value3');

That way you have all of the columns accouted for and no mismatch. It is also better practice to do it this way since you have less chance of the wrong data being placed in a column.

[catacon]

Ok back to the first problem. I want to have (for example) a General category, a General forum, and then I want to display all the topics in the General forum. How would I list all of the topics in the General forum without displaying all the post.

here's another way to look at it:

General category
General Forum
Topic
Posts
Topic
Posts
2nd Forum
Topic
Posts

[mairving]

It depends on how your tables are setup and what the relationships are between categories and forums. Assuming that there is some structure like this:
category_table
category_id
category_title
category_desc

forum_table
forum_id
forum_title
forum_desc
category_id

post_table
post_id
post_title
post_desc
forum_id

You would first have to do a query on the categories:
SELECT category_id, category_title from category_table;
then a sub-query on this:
SELECT forum_id, forum_title from forum_table
WHERE category_id='$category_id';
The category id var would come from the previous select statement.

Then you would do another query to get the posts. This would be a bit more complex since you probably wouldn't want to spit out all of the posts but paginate them instead. The basic query would be the same though.

SELECT post_id, post_title from post_table
WHERE forum_id='$forum_id';

You could use the LIMIT statement in SQL to limit the results to say 5 like so:
SELECT post_id, post_title from post_table
WHERE forum_id='$forum_id'
LIMIT 0,5;

This would limit it to the first 5 records. Of course, doesn't do much good to limit the records if you don't use a sort so you could add:
SELECT post_id, post_title from post_table
WHERE forum_id='$forum_id'
ORDER BY forum_title
LIMIT 0,5;

In this case, oops you are sorting out by title since you didn't add a date column to the posts table. So you really need to think out your database ahead of time.

[catacon]

Thanks, this should work well.

Yet another question (I have alot). I have a page where I can post news updates on my website. I want it so only I can access that page. I also have a login page where it sets two sessions, "user_id" and "first_name", when anybody logs in, but I want to set a certain session for when I login and then have the news page only appear if that session is set. How would I do this.

[mairving]

Originally Posted by catacon Thanks, this should work well. Yet another question (I have alot). I have a page where I can post news updates on my website. I want it so only I can access that page. I also have a login page where it sets two sessions, "user_id" and "first_name", when anybody logs in, but I want to set a certain session for when I login and then have the news page only appear if that session is set. How would I do this.

Not quite as easy. I would like at some of the code in Zend's Code Gallery and adapt it for your own use.

[catacon]

I found a script I can use for the news page in a book I have(PHP and MySQL for the WWW Visual Pro Guide by Larry Ullman), thanks.

For the forum, can I write a script to query the database to select the posts for any given topic? If someone selected topic1, it would print out those posts, if he of she selected topic2 it would print those posts, and so on and so forth. Everything is working great so far, thanks alot for all the help mairving.

[mairving]

For the forum, I really wouldn't try writing your own unless you really wanted the experience. Too many good free ones out there. To come up with a complete forum required hours of work, debugging, testing, community involvement, etc. not to mention the security aspect of things.

[catacon]

Free forums or free scripts?

What security aspect?

[fatboyjim]

Seeing as this thread seems to have died, I'll jump in

Mairving, I'm sure means that there are loads of good free forums out there

Most popular php free forum can be found at www.phpbb.com, it's a piece of cake to install as well.

As for the security update, well, you only need to see how many new releases phpBB bring out to understand that aspect of things. There will always be new exploits found in PHP and probably in the code you've used to build your forum. Could be anything really :P (like allowing people to see your passwords, play with or nuke your database etc)

Jim

[catacon]

Yeah there are a lot of good, free scripts out there. I tried PHPBB and didn't really like it. It is too fancy. I am now using PuBB (punbb.org, i think) and I like it. Simple to use and does not require lots of maintence. Thanks for the posts.

[neouser99]

check out fudforum too...i really like it. did someone say fully-customizable?!

-neo

[catacon]

Hey, another question! I have designed a private messaging system on my website. It works well, but the problem is if user1 sends a PM to user2, the message is sent to user1's inbox. Also, all of user2's settings are set. Any suggestions on how to fix that? I don't have the code right now, but I can post it if you need it.

Cheers, catacon

[catacon]

Anyone? Anyone at all?

[fatboyjim]

How about that code... ?

[catacon]

Sorry this took me so long, but I have been busy with school and whatnot. Anyway, this private message system consists of three (the third which is not shown is a file that deletes the PM and that one works fine). The first shows the users messages and allows them to one. You'll notice that I placed a line of code

print "User id: " .$_SESSION['user_id'] .".";

in the first file (pm.php), this, obviously prints the user's id number. If you remeber, my program works well, but the problem is if user1 sends a PM to user2, the message is sent to user1's inbox, also, all of user2's settings are set. I noticed, through this line of code, that the user id session is changed to user2's id. If you need more explanation, please ask. Here is the code.

pm.php

PHP Code:

<?php

require ("includes/header.php");
require ("includes/nav.php");
require ("includes/right_content.php");


session_start();
if(!isset($_SESSION['user_id'])) {
    header('Location: index.php');
    
    }

$title = "Hall Science Fiction - Private Messages";

?>

<table><tr><td>
<!--Begin Content-->


<!--user pm's-->

<span class="title">Your Messages</span>



<?php

print "User id: " .$_SESSION['user_id'] .".";

require_once ("includes/mysql_connect.php");

             session_start();
   
   $query = "SELECT * FROM pm WHERE user_id={$_SESSION['user_id']} ORDER BY pm_date DESC LIMIT 5";
   
   if ($r = mysql_query ($query)) {
   
         while ($row = mysql_fetch_array ($r)) { 
                 print "<b>Title: {$row['pm_title']} - {$row['pm_date']}</b>
                        <u>Author: {$row['pm_author']}</u>
                  {$row['pm']}
                  --------------------------------
                  
                  <a class=\"nav\" href=\"delete_pm.php?id={$row['pm_id']}\">Delete</a>"; }
                       
       } else {
         die ('We are not working at the moment. Sorry.' . mysql_error());
         
                 
       }

?>

<hr width="50%" />

<!--send pm's-->

<span class="title">Send a Message</span>



<?php


$query = "SELECT user_id, username FROM users ORDER BY username ASC";
$result = @mysql_query ($query);
$pulldown = '<option>Choose Member</option>
';

while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
      $pulldown .= "<option value=\"{$row['user_id']}\">{$row['username']}</option>\n";
}

mysql_close();

?>

<form action="pm_send.php" method="post">

<b>Send To:</b> <select name="user_id"><?php print $pulldown; ?></select>



<b>Title:</b> <input type="text" name="title" size="40" maxlength="40" value="<?php if(isset($_POST['title'])) print $_POST['title']; ?>" />



<b>Message:</b> <textarea name="pm" rows="5" cols="30" value="<?php if(isset($_POST['pm'])) print $_POST['pm']; ?>"></textarea>



<input type="submit" name="submit" value="Send PM" />


</form>


<!--End Content-->
</td></tr></table>

<?php

require ("includes/footer.php");

?>

pm_send.php

PHP Code:

<?php

require ("includes/header.php");
require ("includes/nav.php");
require ("includes/right_content.php");

if(isset($_POST['submit'])) {

        require_once ("includes/mysql_connect.php");
                            
        session_start();
        
    $pm_title = $_POST['title'];
    $pm = $_POST['pm'];
    $pm_author = $_SESSION['username'];
    $user_id = $_POST['user_id'];

    $query = "INSERT INTO pm (pm_title, pm, pm_author, user_id, pm_date) VALUES ('$pm_title', '$pm', '$pm_author', '$user_id', NOW() )";
    
    if (@mysql_query($query)) {
        print 'PM Sent!  <a class="nav" href="pm.php">Back</a>';
        mysql_close();
        
    }else{
    
    print 'ERROR:' . mysql_error() . '!'; }

    
}

require("includes/footer.php");

?>

P.S.
The database fields are: pm_id, pm_title, pm_author, user_id, pm_date. pm_id is the pm number, pm_title is the message title, pm_author is the message author, user_id is the id of the user whom the message was sent to, and pm_date is the date that the message was sent.

Thanks!

[catacon]

Umm...anybody...please...

[catacon]

Please...*sniff*

[neouser99]

just out of curiosity, b/c everything up there looks ok, what is in the file mysql_connect?

-neo

[catacon]

What it does? It connects to MySQL. I can't remember what the code is, but I could get it. It's on my other comp. I am still really confused by all of this, like you said, all the code looks fine, but there is still a problem.

[neouser99]

the only thing that i can think of, which you seem to have already worked around, but might still be popping its head in there somewhere...

session handling has some big issues with redirecting, but like i said. it seems that you have already witnessed that and put some workarounds in, so i am not sure what the problem seems to be. the reason that i ask about that file is just to see what you do beyond connecting to the db, but if all you are doing is a mysql_connect with some variables, not a huge deal.

-neo

[catacon]

I found the problem, and fixed it. In the pm_send.php file, there is the variable user_id, which tells mysql which user to send the pm to. However, there is also a session named user_id. I changed the user_id variable to receiver_id and that fixed it. I also changed the pm_author variable to just author and all seems well. Thanks for all the help guys! Now, hopefully, I can put my site online.