Can I get a database sketch based on this information?

wolfcry044

I used to have a PHP/SQL web designer for my gaming clan, but he left. He made a site, but I accidently deleted the database for it because it was using the same database as the forums, and back then I did not know how it all worked. I've been learning PHP for about 4 days now, and have an ok grasp on how things work.

I'm going to post here the my old web designers code for the Roster page. Can someone give me a sketch as to how the database would have looked, by looking at the information I'm about to provide? I just need to know how to create the database again, so that I can start unraveling what he did, and learn how he did it.

Thanks!

<?php require_once('Connections/conn_local.php'); ?>
<?php
mysql_select_db($database_conn_local, $conn_local);
$query_rs_cadet = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value > 20 AND rank_value < 25 AND user_onleave = 0 ORDER BY rank_value ASC, username ASC";
$rs_cadet = mysql_query($query_rs_cadet, $conn_local) or die(mysql_error());
$row_rs_cadet = mysql_fetch_assoc($rs_cadet);
$totalRows_rs_cadet = mysql_num_rows($rs_cadet);

mysql_select_db($database_conn_local, $conn_local);
$query_rs_juniors = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value > 12 AND rank_value < 21 AND user_onleave = 0 ORDER BY rank_value ASC, username ASC";
$rs_juniors = mysql_query($query_rs_juniors, $conn_local) or die(mysql_error());
$row_rs_juniors = mysql_fetch_assoc($rs_juniors);
$totalRows_rs_juniors = mysql_num_rows($rs_juniors);

mysql_select_db($database_conn_local, $conn_local);
$query_rs_seniors = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value > 4 AND rank_value < 13 AND user_onleave = 0 ORDER BY rank_value ASC, username ASC";
$rs_seniors = mysql_query($query_rs_seniors, $conn_local) or die(mysql_error());
$row_rs_seniors = mysql_fetch_assoc($rs_seniors);
$totalRows_rs_seniors = mysql_num_rows($rs_seniors);

mysql_select_db($database_conn_local, $conn_local);
$query_rs_command = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value > 0 AND rank_value < 5 AND user_onleave = 0 ORDER BY rank_value ASC, username ASC";
$rs_command = mysql_query($query_rs_command, $conn_local) or die(mysql_error());
$row_rs_command = mysql_fetch_assoc($rs_command);
$totalRows_rs_command = mysql_num_rows($rs_command);


mysql_select_db($database_conn_local, $conn_local);
$query_rs_special = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value = 99 AND user_onleave = 0 ORDER BY rank_value ASC, username ASC";
$rs_special = mysql_query($query_rs_special, $conn_local) or die(mysql_error());
$row_rs_special = mysql_fetch_assoc($rs_special);
$totalRows_rs_special = mysql_num_rows($rs_special);


mysql_select_db($database_conn_local, $conn_local);
$query_rs_onleave = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value > 0 AND rank_value < 25 AND user_onleave = 1 ORDER BY rank_value ASC, username ASC";
$rs_onleave = mysql_query($query_rs_onleave, $conn_local) or die(mysql_error());
$row_rs_onleave = mysql_fetch_assoc($rs_onleave);
$totalRows_rs_onleave = mysql_num_rows($rs_onleave);

mysql_select_db($database_conn_local, $conn_local);
$query_rs_boxinfo = "SELECT box_headline, box_content FROM website_boxes WHERE box_id = 10";
$rs_boxinfo = mysql_query($query_rs_boxinfo, $conn_local) or die(mysql_error());
$row_rs_boxinfo = mysql_fetch_assoc($rs_boxinfo);
$totalRows_rs_boxinfo = mysql_num_rows($rs_boxinfo);

?>

sid

you pretty much can adapt most of the queries to be create table statements - see more information on how to do this here: http://dev.mysql.com/doc/refman/5.0/en/create-table.html (scroll down for examples)

however - it is impossible to tell which data types or default values, auto increment, keys the fields and tables had. i would try to give each field a LONGTEXT type initially and work from there. i assume that a lot of INTEGER, VARCHAR and TEXT fields will be left in the end. but like i said - it's not really possible (at least from these queries alone) what data type is supposed to be used.

in case you are not using it yet, check out phpMyAdmin - this will allow you to follow through with changes in the database a lot faster than by command line/executing single queries!

hope this helps!
sid

wolfcry044

Thanks. I just need a basic structure, that's all. I'm using PHPMYADMIN. If I know what his basic structure is [just the skeliton of the database layout is all] then I will be able to go from there.
Thanks!

sid

the important parts for you in the SELECT query is

SELECT [field_names] FROM [database_table]

everything after that may give you extra information about each fields data type.
assuming that the queried field names are all of that table, you should be able to re-create the rough db-structure. however, since there don't seem to be all not all too many id's, i am not sure if the field name lists are indeed complete.

wolfcry044

I believe he is taking from two tables in the forums database. I have made a sketch as to what I believe the database might have looked like before. I'm not sure if this is accurate, I have only been doing this for a few days, so I'm still very shaky on how things work in PHP.

Here's the sketch I made according to what I have shown above. Does it look accrate as far as you can tell? There is much more data in each table, because they are forum tables, but it looks like this is what he was drawing from for the roster page:

Command- Not in the database, is PHP only

phpbb_users <---Table

phpbb_ranks <---Table

user_id <--- Fields
username
user_rank
user_image
rank_id
rank_title
rank_image
rank_value
user_onleave

rincewind456

You have three tables in use in that list of queries they are

Table Name: phpbb_9999ranks
Fields:	rank_id
	rank_image
	rank_title
	rank_value

Table Name: phpbb_9999users
Fields:	user_id
	user_image
	user_onleave
	user_rank
	username

Table Name: website_boxes
Fields:	box_headline
	box_content
	box_id

As well as the other tables described in your other post on this subject.

If you want advice about what characteristics to assign to each field then you would need to supply information about what data was going to be inserted into the database.

wolfcry044

Thank you!
This particular page is a Roster page, which will be listing members of my clan, and their ranks on the forums. When the forums ranks change on the members, so will the website as well. It should be drawing from the forum ranks. There is different categories, like Command ranks, Senior ranks, Junior ranks, cadet ranks, on leave ranks, and maybe some that I missed. That gives basically the idea of it though.