PHP - Database Design Help

I have spent lots of time trying to design a nice structure for my project and I would like some feedback to what you guys think, please note this is the first time I've ever done anything like this.   About My Project I am creating a service where advertisers can search through a catalog of websites they want to directly advertise on. Once they have found a website, they can upload an image, pay the fees and start advertising on that website instantly.   I have designed an image of how I think the structure should look, I have designed it this way for efficiency but I am pretty sure I could improve with some help. If you need more info or have any questions, please ask.   Project Structure Design Image   Thanks for reading, what do you think about this structure? Can I improve it?
Edited by itsliamoco, 26 July 2014 - 09:34 PM.

Hi guys, I don't know if I'm over complicating things here and cannot see the wood for the trees, but I seem to have got myself confused. What I'm simply trying to do is get teamName and teamresults of a team from a database.  The code will perhaps explain better than I can (note I haven't included the DB class which is fine).

class TeamCollection {
    protected $database;
    protected $teamid;
    protected $team_name;
    
    // pass db by dependency injection...
    public function __construct(Database $database) {
            $this->database = $database;    
    }

    public function getTeam($teamid){
            $this->team_id = $teamid;
            $this->database->query("SELECT  team_name, nickname, founded FROM club WHERE team_id=:teamid");
            $this->database->bind(':teamid', $this->team_id);    
            
        // spawn a new Team object if query is valid, if not throw exception and end via try/catch...
        if ($result = $this->database->single()) {
                return new Team($result); // create new Team passing $result to Team constructor
        }
        else {
                throw new exception ('No Valid Team returned');
        }
    }
    
    public function getResults($teamid){
            $this->team_id = $teamid;
            $this->database->query("SELECT result FROM results WHERE team_id=:teamid");
            $this->database->bind(':teamid', $this->team_id);    
    
        // If a Team has valid results, return array of match scores..
        if ($scores = $this->database->results()) {
                return $scores;
        }
        else {
            throw new exception ('No Valid Team Results to return');
        }
    }

} //end class

// Team is abstracted from DB
class Team {
    
    private $team_name;
    private $result;
    private $scores;
    
    public function __construct(array $result) {
        $this->team_name = $result['team_name'];
    }
    
    public function getName() {
        $this->team_name;
    }
    
    public function getResults(array $scores) {
        foreach ($scores as $row){
            echo $row;    
        }
    }
}
// implementation

$team_id =9; // passed variable..

$database = new Database($server,$db_type); // create db..

$database->connect(); // connect to db..

$collection = new TeamCollection($database); // pass live DB link to TeamCollection Class

try {
    
    $team = $collection->getTeam($team_id); // try and spawn a valid Team Object..

    echo $name = $team->getName(); // If so lets return a Team name

    $scores = $team->getResults($team_id); // Now try and get Team Results..

    $display_results = $scores->getResults($scores); // Display results by passing them to Team class to

    }

catch (Exception $e) {
    Echo 'Exception Thrown: ' . $e->getMessage();    
}

My basic difficulty is where to put the getResults method and how to use it? I'm fine spawning a new Team as required, but is my logic for then moving on to getResults sound? I was trying to abstract my Team class from having any database association at all. Is this a wise approach or just not required? Am I overcomplicating the issue or missing something? Can anyone help? I guess my difficulty is between connecting classes and objects in the best manner. Thanks in advance.
Edited by mich2004, 19 May 2014 - 06:48 AM.

Hello I have a question in regards to design. No need for example code just of how to approach the problem.

Issues and things that cannot change because they are part of the situation.
1. Cannot use any database like oracle, MySQL, or SQLite
2. The server does not run PHP5
3. The students do not have a unique field and cant have one because they have not my employers have not decided on one.

Here is the problem:
I have to make a form with the following fields

first name
last name
comments
date

I have stored the fields in a text file.
However, how can I relate date to the comment  without having duplicates records?
For example. Lets suppose my boss asked me  can you look up the comments that the counselor made in july 10 2010.

My answer: One must have duplicate records of each instance of a comment. Am I wrong?

Hi,

For the last few years on my website I have been using FluxBB/PunBB's login script which basically just stores a cookie with their user ID and their password hash. This is fine except if the database got leaked anyone can login as anyone else providing they have their hash.

I'm trying to be more secure about this now. Can someone explain a better process to me please?

I was thinking of storing sessions with a unique hash in an 'active_sessions' table and providing they have a cookie with that hash they are granted that session? If someone uses a different IP to what that session was created with it's deleted?

I'm new to all of this so any advice would be great. I want this to be secure.

Hi All,

I currently have a ticketqueue that show's all tickets assigned to a group of people, but split into personal queues, but the way that I wrote it, means that it needs manually updating if a specific person leaves/joins the department. For example, to get the queue details, I use the following query:

Code: [Select]
$username1 = mssql_query("select id,subject,body,priority from queue where assignedto = username1";)
$username2 = mssql_query("select id,subject,body,priority from queue where assignedto = username2";)

I have repeated this code for all of the users in our team. Which seams a waste, as I have all the information on our team stored in a DB called "sysadminusers". Is there an array I could use that would look at all the usernames in the table, and then repeat the query for me?

I would also need this array to display the results on the page, currently I use the following:

Code: [Select]
while($username1_tickets = mssql_fetch_assoc($username1)){
echo $username1_tickets['id'],$username1_tickets['subject']$username1_tickets['body'],$username1_tickets['priority'];}
while($username2_tickets = mssql_fetch_assoc($username2)){
echo $username2_tickets['id'],$username2_tickets['subject']$username2_tickets['body'],$username2_tickets['priority'];}

I am just looking for some design advice and code examples that would help me tidy up my code for this page, it seams a lot of code for quite a simple page.

Thanks
Matt

Amazingly, threads about application design belong in the Application Design sub-forum.

This topic has been moved to Application Design.

http://www.phpfreaks.com/forums/index.php?topic=358384.0

Hi there,   This may not be the correct space to post this - but I'm very new to all this.   I've created a page that will run a php script to output a query. I would then like to send that same query via email - however from a design perspective I don't think the correct process is to click 'submit' to view the query and then 'submit' to send the result via email?   Can you please advise on some design solutions regarding this issue?   Thanks!  

I have a php file, call it "mypage.php"
it makes come calculations, create 5 different arrays,
and creates a table by using the arrays.

I found simpleXML to create XMLs easily.
I want to make an xml file by using the contents of mypage.php.

I want to do that with an argument like that "mypage?type=xml"
I'm aware that i should use "$_GET" or "$_POST"
But where should i put the php code in page,
so when i use argument "xml" it will create an xml file.
When i don't give argument it will create an html type file.

Or should i use another php that includes mypage.php??
And get arrays from that file?

Thank you.

Hi all!

I'm a real noob and this is my first time making a website and I haven't hosted it yet.
I'm trying to make a website that is fast and secure.
My pages look something like this.

Code: [Select]
<?php include("header.php");include("sidebar.php"); ?>
HTML CODES
<?php include("footer.php"); ?>

1. Are these php files cached after their first load? When I go to another page, those parts seem to stay still.
2. Is this code secure?

And now I'm starting to make product pages which will be around 40 pages.
So I'm considering something like  this...

Code: [Select]
<?php include("header.php");include("sidebar.php"); ?>
<a href="index.php?page=a">Page A</a>
<?php 
$page = $_GET['page'];
if (file_exists('pages/'.$page.'.php'))
{
   include('pages/'.$page.'.php');
}
?>
<?php include("footer.php"); ?>

3. Is it better to make every page like this since it will load header, sidebar, footer only once?
4. How should I protect from the user input data, by making an array of allowed files or by prohibiting "." and "/" ?

Thank you in advance.

sorry guys its been a long day and this is an extreme newbie question but for whatever reason right now I cant wrap my head around the concept right now...
say for instance one wanted to make a db for job postings, where one could go and see many different categories etc etc. Would you create a table "jobs" and then create rows for the types ie) Accounting, Business, Customer Service, etc. - or would those be all seperate tables as well? Cause im just thinking of the INSERT INTO query, and say I wanted to add a job but I only wanted to add one to the Customer service portion of the jobs table...thats why im curious if everything should be different tables so you could just do INSERT INTO customer_service etc etc and then have all of the persons names/creditials there? OR is it possible to create a table within a table? or am I simply going nuts here? lol. thanks

At the moment  I am creating a search function for my website.
The approach I have in mind is a pseudo-PHP database. To give an example:

A HTML form will submit the results to a PHP file.

HTML FORM - Colour: Black
PHP RESULT PAGE - if ($_POST['color'] == 'Black') {readfile("./products/black/*.html");}
HTML FORM - Price: <$50
PHP RESULT PAGE - if ($_POST['Price'] == '<$50') {readfile("./products/less50/*.html");}

The problem here is if there is an item that is black and costs less than $50, then its going to be listed twice. There is probably some code I can write to ommit the listing of duplicate entries, but it is probably going to be messy, so I am wondering if its better to use a centralized MySQL database, rather than a pseudo-PHP database? I've never used MySQL and don't know much about it and this is my first real attempt at using PHP.

hello everyone,
I am about to start coding my pages to display results from a database but before i do i want to know information about the following :

Is it best to upload images to a database?and display them accordingly? or is it best to use images from a directory? What is most commonly used and or more reliable?

Another topic i have trouble finding information on is actually positioning the output from you mysql database, is this practice done with tables?fields and rows? What is this method called? And is there more then one way to go about controlling result layout on your page?

Sorry about the 1001 questions , but i am unable to find a clear answer on the topic ..especially question two .
Thanks in advance.

after cloasing connection of database i still got the values form database.

Code: [Select]
<?php
session_start();
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
require_once '../database/db_connecting.php';
$dbname="sahansevena";//set database name
 $con=  setConnections();//make connections use implemented methode in db_connectiong.php
 mysql_select_db($dbname, $con);
//update the time and date of the admin table
 $update_time="update admin set last_logged_date =CURDATE(), last_log_time=CURTIME() where username='$uname'limit 3,4";
 //my admin table contain 5 colums they are id, username,password, last_logged_date, last_log_time
 $link= mysql_query($update_time);
 // mysql_select_db($dbname, $link);
//$con=mysql_connect('localhost', 'root','ijts');

       $result="select * from admin where username='a'";

        $result=mysql_query($result);
mysql_close($con);
//here i just check after closing data baseconnection whether i do get reselts but i do, why?
echo "after the cnnection was closed";
if(!$result){
echo "cont fetch data";

}else{

   $row= mysql_fetch_array($result);
   echo "id".$row[0]."usrname".$row[1]."passwped".$row[2]."date".$row[3]."time".$row[4];
}
//  echo "<html>";
//echo "<table border='1' cellspacing='1' cellpadding='2' align='center'>";
//      echo "<thead>";
//           echo"<tr>";
//                   echo "<th>";
//                   echo ID;
//                   echo"</th>";
//                   echo" <th>";echo Username; echo"</th>";
//                    echo"<th>";echo Password; echo"</th>";
//                    echo"<th>";echo Last_logged_date; echo "</th>";
//                   echo "<th>";echo Last_logged_time;  echo "</th>";
//               echo" </tr>";
//           echo" </thead>";
//           echo" <tbody>";
//while($row= mysql_fetch_array($result,MYSQL_BOTH)){
//             echo "<tr>";
//              echo "<td>";
//              echo $row[0];
//               echo "</td>";
//                   echo "<td>";
//              echo $row[1];
//              echo "</td>";
//                  echo "<td>";
//              echo $row[2];
//               echo "</td>";
//                   echo "<td>";
//                echo $row[3];
//              echo "</td>";
//                  echo "<td>";
//              echo $row[4];
//               echo "</td>";
//            echo "</tr>";
//     }
//         echo" </tbody>";
//         echo "</table>";
//         echo "</html>";
     session_destroy();
session_commit();

echo "session and database are closed but i still get values from doatabase session is destroyed".$_SESSION['admin'];
?>

session is destroyed but database connection is not closed.

thanks

I have a very tricky php problem here. At least for me.
On a page that is editing a job entry. I have a database generated group of checkboxes some of which have been checked when the job was entered.

So the script has to do two things generate the complete list of checkboxes and check the ones that have already been selected in the job request. The below script is what I've done trying to figure it out.

Description of what I'm trying to do:
I'm generating the the boxes with a call to my database for the list of checkboxes.
Then I'm building an array with all the possible items that could be checked to compare against.
 
Ok here is where I think my first problem is. I need to make a second query to another table "worklog" in the same database. This is where the list of checked items are stored. What is the best way to do this? A second query seems wrong "or at least not efficient"

       Code: [Select]
<div class="text">Job Type&nbsp;(<a class="editlist" href="javascript:editlist('listedit.php?edit=typelist',700,400);">edit list</a>):</div>
<div class="field">

    <?PHP

    $connection=mysql_connect ("localhost", "user", "password") or die ("I cannot connect to the database.");
    $db=mysql_select_db ("database", $connection) or die (mysql_error());
    $query = "SELECT type FROM typelist ORDER BY type ASC";
    $sql_result = mysql_query($query, $connection) or die (mysql_error());
    $i=1;
    echo '<table valign="top"><tr><td>';
    while ($row = mysql_fetch_array($sql_result)) {
    $type = $row["type"];
        if ($i > 1 && $i % 26 == 0)
        echo '</td><td>';
        else if ($i)
        echo '';
        ++$i;
       
        $aTypes = array ("Spec Ad Campaign", "100 x 100 Logo", "100 x 35 (Featured Developer/Broker)", "100 x 40 (Featured Lender)", "120 x 180 (Home Page Auto)", "120 x 45 (Half Tile - Jobs)", "120x60 (Section Sponsor)", "125 x 40 (Profile Page logo)", "135 x 31 (Autos)", "135 x 60 (Autos)", "135 x 60 (Logotile - Jobs)", "150 x 40 (Auto Logo)", "160 x 240 (monster tile)", "160 x 400 (Skyscraper)", "160 x 600 (Tower)", "170x30 (Section Sponsor)", "240 x 180 JPG/GIF Auto Video", "240 x 180 size FLV video ", "300 x 250 (Story Ad)", "300 x 600 (Halfpage)", "468 x 60 (Banner Jpg/Gif)", "728 x 90 (Leaderboard)", "95 x 75 (Sweeps Logo) ", "Agent Profile Page", "Contest", "Creative Change", "Employer Profile", "Holiday Shop", "Home of the Week", "HP Half Banner (234 x 60 Static)", "Mobile Ads (320x53 *300x50*216x36*168x28)", "Newsletter", "Peelback", "Pencil Ad", "Print", "Real Deals", "Resize ad campaign", "Roll-Over Skyscraper", "Site Sponsor logo (170x30)", "Splash Page Production", "Travel Deals Update", "Video Ad", "Web Development", "Web Maintenance", "Web Quote (or Design)");
       
        $dbTypes = explode(',',$row['type']);
       
        foreach ($aTypes as $type){
       
        if(in_array($aType,$dbTypes)){
       
      echo "<input style='font-size:10px;' name='type[]' type='checkbox' value='$type' CHECKED><span style='color:#000;'>$type</span></input><br/>";
       
        }else
       
        {
       
            echo "<input style='font-size:10px;' name='type[]' type='checkbox' value='$type'><span style='color:#000;'>$type</span></input><br/>";  
       
               
        }

        }

    }
      echo '</td></tr></table>';
   
    ?>

</div>

I have a standard form that displays users current data from a mysql database once logged in(code obtained from the internet). Users can then edit their data then submit it to page called editform.php that does the update. All works well except that the page does not display the updated info. Users have to first logout and login again to see the updated info. even refreshing the page does not show the new info. Please tell me where the problem is as i am new to php.   my form page test.php

<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<form action="editform.php?id_user=<?= $fgmembersite->UserId() ?>" method="POST">
<input type="hidden" name="id_user" value="<?= $fgmembersite->UserId() ?>"><br>
Name:<br> <input type="text" name="name" size="40" value="<?= $fgmembersite->UserFullName() ?>"><br><br>
Email:<br> <input type="text" name="email" size="40" value="<?= $fgmembersite->UserEmail() ?>  "><br><br>
Address:<br> <input type="text" name="address" size="40" value="<?= $fgmembersite->UserAddress() ?>  "><br><br>

<button>Submit</button>

my editform.php

<?php

$con = mysqli_connect("localhost","root","user","pass");

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_query($con,"UPDATE fgusers3 SET name = '".$_POST['name']."', email= '".$_POST['email']."', address= '".$_POST['address']."' WHERE id_user='".$_POST['id_user']."'");



header("Location: test.php");

?>