PHP - Database Schema Design Question

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.

So, built a classifieds site.   The link is something like this: mysite.com/category/subcategory/1234   The category & subcategory don't do anything, they are just for vanity.   My question is about the '1234'. The number corresponds to an "id" which is a unique key in my database.   When people make a post, I use LAST_INSERT_ID() to get the most recent post and generate the URL.   So basically, my urls correspond 100% to database ID's, for better or worse. These go up by 1 with each post. I anticipate some potential numbering issues when I eventually delete posts.   Would it be a better idea to generate these unique IDs some other way, or is this acceptable design?   Thanks.
Edited by arbitrageur, 19 November 2014 - 11:13 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?

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

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, here is my validateXML function, it checks first if an XML file is well-formed and result $result is true, then if well-formed and schema provided, it does further check to see if schema validated and result $result returns true too (using php core function: schemaValidate(string $schemaFileName), so my let's say I use stuff.xml and houses.xsd, so even if stuff.xml is well formed, and the function goes to evaluate houses.xsd(assume also validated), then the $result of true doesn't really indicate that the well formed stuff.xml is also schema validated since schemaValidate only checks if a schema itself is validated, BUT how does it link the validated schema to the well-formed stuff.xml.

In short, I am saying I can pass a wellformed xml file that with a validated schema file BUT the two may be totally different.

Here is the function:
Code: [Select]
</php
public function validateXML($xmlFilename, $xmlSchema=null)
    {
        $result = false; //which line you don't understand
        $dom = new DOMDocument();
        if ( $dom->load($xmlFilename) || $dom->loadXML($xmlFilename))//so this tests if it is well formed?yes
        { //and if false, $result is false
            //the xml is well-form, now test schema
            $result = true;
            if ( $xmlSchema ) // if we don't pass schema , mean we don't need to test the shcme, the validate will return true still
            {               
                $result = $dom->schemaValidate($xmlSchema);//returns true on success
  }           
        }     
        //error occurrs, if there is not erro, this code will not run, because errors is empty array
        $errors = libxml_get_errors(); //stores each line as array elem
foreach ($errors as $error) {
print $this->showLibXMLErrors($error);
}
libxml_clear_errors();
        return $result;//1 is TRUE, 0 is FALSE   
    }
?>

I'd appreciate any help!

XSD <?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <xsd:simpleType name="MgmtCodetyp">
        <xsd:restriction base="xsd:string">
             <xsd:pattern value="[A-Z][A-Z]([A-Z]|[0-9])"></xsd:pattern>
        </xsd:restriction>
    </xsd:simpleType>

    <xsd:element name="MgmtCode" type="MgmtCodetyp"></xsd:element>
</xsd:schema>   XML <MgmtCode>AGF</MgmtCode>   PHP Code <?php

        $file = "data.xml";

        function libxml_display_error($error) {

                $return = "\n";

                switch ($error->level) {
                        case LIBXML_ERR_ERROR:
                                $return .= "[ERROR] ";
                        break;
                        case LIBXML_ERR_FATAL:
                                $return .= "[FATAL] ";
                        break;
                        default:
                                $return .= "[UNKNOWN] ";
                }

                $return .= trim($error->message);
                $return .= " [line: $error->line]\n";

                return $return;
        }

        function libxml_display_errors() {

                $errors = libxml_get_errors();

                foreach ($errors as $error) {
                        if ($error -> level != LIBXML_ERR_WARNING) {
                                print libxml_display_error($error);
                        }
                }

                libxml_clear_errors();
        }

        // Enable user error handling
        libxml_use_internal_errors(true);

        $xml = new DOMDocument;
        $xml -> load("kkk.xml");
        if (!$xml -> schemaValidate("kkk.xsd")) {

                libxml_display_errors();
        }
        else {
                
                print "no error\n";
        }

?>   OUTPUT [ERROR] Element 'MgmtCode': [facet 'pattern'] The value 'AGF' is not accepted by the pattern '[A-Z][A-Z]([A-Z]|[0-9])'. [line: 1]

[ERROR] Element 'MgmtCode': 'AGF' is not a valid value of the atomic type 'MgmtCodetyp'. [line: 1]   ANALYSIS The XML is a perfectly valid xml. I don't understand why it failed the XSD.

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");

?>

If you are using Vertabelo for creating database models and you access your database with Propel library, you'll find the following instructions useful.

Hey guys.. I've been setting up a database for a contact page and I have everything working for it except the following error on my update contact page.. can anyone help me figure it out?? The error lines are the ones where I have my mysql_result's.

Errors:

No Records Found

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 103

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 104

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 105

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 106

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 107

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 108

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 109

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 110

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/jonnyp22/public_html/a5/update_contact.php on line 111

Code: [Select]
<!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 content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Update Contact</title>
<script  type="text/javascript">
function Validateform(){
var email=document.form1.email;
var firstname=document.form1.firstname;
var lastname=document.form1.lastname;
var state=document.form1.state;

var reEmail = /^(?:\w+\.?)*\w+@(?:\w+\.)*\w+$/;

if ((email.value==null)||(email.value=="")){
alert("Please enter email address");
email.focus();
return false;
}
if (reEmail.test(email.value)==false){
  alert ("Please enter valid email address");
    email.focus();
    return false;
    }
if ((firstname.value=="")||(firstname.value==null)) {
alert("Please enter first name");
firstname.focus();
return false;
}

if ((lastname.value=="")||(lastname.value==null)) {
alert("Please enter last name");
lastname.focus();
return false;
}
if (state.selectedIndex==0) {
alert("Select state");
state.focus();
return false;
}
return true;
 }

</script>

<style type="text/css">
.style1 {
text-align: center;
color: #F8B57E;
font-size: x-large;
}
.style2 {
text-align: center;
}
.style3 {
text-align: right;
color: #F8B57E;
}
.style4 {
text-align: left;
}
.style6 {
text-align: center;
color: #F8B57E;
font-size: medium;
}
.style7 {
color: #FFFFFF;
}
</style>
</head>

<body style="color: #FFFFFF; background-color: #102541">

<div class="style2">

<p class="style1"><strong>Update Contact</strong></p>
<p class="style6">* Indicates a Required Field</p>

<?php

include("dl.php");

// If the form has not been submitted then show it

if(!$_POST['Submit'])
{

$email=$_GET['email'];

$query="SELECT * FROM person WHERE Email = '$email'";
$result=mysql_query($query) or die('Error: ' . mysql_error());

$num=mysql_numrows($result);

mysql_close();

if ($num == 0) {
echo "<b><center>No Records Found</center></b>";
}

$lastname=mysql_result($result,0,"Last_Name");
$email=mysql_result($result,0,"Email");
$firstname=mysql_result($result,0,"First_Name");
$address1=mysql_result($result,0,"address1");
$address2=mysql_result($result,0,"address2");
$city=mysql_result($result,0,"city");
$state=mysql_result($result,0,"state");
$zip=mysql_result($result,0,"zip");
$phone=mysql_result($result,0,"phone");

?>

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<table style="width: 100%">
<tr>
<td style="width: 219px; height: 26px" valign="top" class="style3">E-mail Address:</td>
<td style="height: 26px" class="style4">
<input name="email" style="width: 363px" type="text" value="<? echo $email; ?>" readonly="readonly" />
</td>
</tr>
<tr><td class="style3">First Name:</td><td class="style4"><input type="text" name="firstname" id="firstname" size="30" width="250px" value="<? echo $firstname; ?>"/></td></tr>
    <tr>
<td class="style3" >Last Name:</td><td class="style4" ><input name="lastname" id="lastname" type="text"  size="30" width="250px" value="<? echo $lastname; ?>"/></td>
</tr>
    <tr><td class="style3">Phone:</td><td class="style4"><input type="text" name="phone" id="phone" size="15" width="250px" value="<? echo $phone; ?>"/></td></tr>
    <tr>
      <td class="style3">Address Line 1:</td><td class="style4"><input type="text" name="address1" id="address1" size="50" width="250px" value="<? echo $address1; ?>" /></td></tr>
    <tr>
      <td class="style3">Address Line 2:</td><td class="style4"><input type="text" name="address2" id="address2" size="50" width="250px" value="<? echo $address2; ?>"/></td></tr>
    <tr><td class="style3">City:</td><td class="style4"><input type="text" name="city" id="city" size="50"  width="250px" value="<? echo $city; ?>"/></td></tr>
    <tr><td class="style3">State:</td><td class="style4"><select name="state" id="state">
   
<option <? if ($state=="NJ") echo selected ;?> >NJ</option>
<option <? if ($state=="NY") echo selected ;?> >NY</option>

</select></td></tr>
    <tr><td class="style3">Zip:</td><td class="style4"><input type="text" name="zip" id="zip" size="5" value="<? echo $zip; ?>"/></td></tr>
</table>
<p class="style2"><input name="Submit" type="submit" value="Update" />&nbsp;<a href="show_contacts.php"><span class="style7">Show Contacts</span></a>
<br />
</p>
</form>

<?php

}
else
{

// The form has been submitted so process it

$email=$_POST['email'];
$lastname=$_POST['lastname'];
$firstname=$_POST['firstname'];
$phone=$_POST['phone'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$city=$_POST['city'];
$state=$_POST['state'];
$zip=$_POST['zip'];

$query="UPDATE person SET Last_Name='$lastname',FIRST_Name='$firstname',Phone='$phone', Address1='$address1', Address2='$address2', city='$city', state='$state',zip='$zip' WHERE Email='$email'";

if (!mysql_query($query,$con))
  {
   die('Error: ' . mysql_error());
  }
else {
echo "<center><b> Contact info $lastname updated successfully </b><a href='show_contacts.php'>show contacts</a></center>";
}

mysql_close();

}
?>
</div>
</body>

</html>

Hi

Just want some advice on how to do the below:

I already have one table called 'venopt' which has two fields one is 'id' and 'venues'

I also already have a table for members details called 'ptdata' and has 'id' 'firstname' and 'surname' etc

Both ids in each table are primary keys and autoincremented.

The user will be selecting options from the 'venues' table and submitting them via POST.

How should i then handle the data, is it best to setup another table which will store the selections and if so how would i go about that?

Or is it best to store the details in an array say in the 'ptdata' table.

Either way I would also want to retrieve the contents of the array and display it in another drop down list?

Any ideas

Is there any other way of getting PHP form data into C# any other way besides calling  

www.downloadHandler.text

I am having issues  bringing all the entries into C# and breaking them all up.  I can do one row fine but multiple rows isn't working.  I keep getting an out range error.

This is my PHP echo

echo $row['userName']. '|' .$row['level']. '|' .$row['points']. '|' .$row['killRate']. '/';

And this is my C# code

                string nothing = "Not Placed";
                string Data_string = www.downloadHandler.text;
                string[] DataArray;
                DataArray = Data_string.Split('/');

                int numberOfEntries = DataArray.Length;
                Debug.Log(numberOfEntries);

                if (DataArray[0] == null || numberOfEntries == 1)
                {
                    DataArray[0] = nothing;
                    Debug.Log("Data Array [0] isn't there");
                    High_Points_1.text = DataArray[0];

                }
                else
                {
                    High_Points_1.text = DataArray[0];
                    //Debug.Log(DataArray.Length);
                }
                if (DataArray[1] == null || numberOfEntries == 2)
                {
                    DataArray[1] = nothing;
                    Debug.Log("Data Array [1] isn't there");
                    High_Points_2.text = DataArray[1];
                }
                else
                {
                    High_Points_2.text = DataArray[1];
                    //Debug.Log(DataArray.Length);
                }
                if (DataArray[2] == null || numberOfEntries == 3)
                {
                    DataArray[2] = nothing;
                    Debug.Log("Data Array [2] isn't there");
                    High_Points_3.text = DataArray[2];
                }
                else
                {
                    High_Points_3.text = DataArray[2];
                }
                if (DataArray[3] == null || numberOfEntries == 4)
                {
                    DataArray[3] = nothing;
                    Debug.Log("Data Array [3] isn't there");
                    High_Points_4.text = DataArray[3];
                }
                else
                {
                    High_Points_4.text = DataArray[3];
                }
                if (DataArray[4] == null || numberOfEntries == 5)
                {
                    DataArray[4] = nothing;
                    Debug.Log("Data Array [4] isn't there");
                    High_Points_5.text = DataArray[4];
                }
                else
                {
                    High_Points_5.text = DataArray[4];
                }

I have two entries in the database.  But when I debugged the number int he array I get 3 strings.

I want to show the top five entries in the database.

quick question... I have a column in my table called views. I want to reset the column to 0 for each row in the table. What's the easiest way to do this?

I created a database class to connect to a database.  The code is below.  I'm not sure how to call this connection in other classes.  Do I use:

$db->pdo = $conn->prepare($sql);

or what?  Note that the db object is instantiated at the end of the class file.  Here is the class:

 

class DB {
	
	public $pdo = '';
	//public $message = 'A message from db'; // Debug

function __construct() {
	
	// Database info located elsewhere
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "dbname";
	
	try {
		
	  $this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	  // set the PDO error mode to exception
	  $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	}  // End Try
	
	catch(PDOException $e) {
		  echo "Error: " . $e->getMessage();
		}
		
		//echo '<h3>Everything wnet OK.</h3>';  // Debug

	} // End __construct
	
} // End class definition DB.php

$db = new DB;

Thanks,

 

--Kenoli

hello.

i have a question for you.

say i have a group of tick boxes and i select a couple.

would i put them in to the database as varchar ?
like this:

id   varchar
1    1, 2, 3

or

id   varchar
1    '1', '2', '3'


then my next question is how would i use them.
i guess i would explode them or something.

the thing is, i have some code that currently pull 1 item from the database (using varchar) and echos it fine but when i add more i get nothing back.

thanks
rick