PHP - Mysql Tables Relationship With A Foreign Key On Parent Table

SET UP: Windows vista
# XAMPP 1.7.3,
# Apache 2.2.14 (IPv6 enabled) + OpenSSL 0.9.8l
# MySQL 5.1.41 + PBXT engine
# PHP 5.3.1
# phpMyAdmin

Problem: The name_id column from the card_numbers table doesn't increment at all. Even if if I put in two rows of data I'm left with the name_id not increasing.

Main Goal: I want that column to increment with the same values as the other tables.

Code for Mysql database:

<?php
require 'db.inc.php';

$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

$query = 'CREATE TABLE IF NOT EXISTS subscriptions (
name_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email_address VARCHAR(50),
membership_type VARCHAR(50),

PRIMARY KEY (name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the user information table
$query = 'CREATE TABLE IF NOT EXISTS site_user_info (
name_id INTEGER UNSIGNED NOT NULL,
terms_and_conditions VARCHAR(50) NOT NULL,
name_on_card VARCHAR(50),
credit_card_number VARCHAR(50),

FOREIGN KEY (name_id) REFERENCES subscriptions(name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

$query = 'CREATE TABLE IF NOT EXISTS card_numbers (
name_id INTEGER UNSIGNED NOT NULL,
credit_card_expiration_data VARCHAR(50),

FOREIGN KEY (name_id) REFERENCES subscriptions(name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

echo 'Success!';
?>


Code of the data being inserted into mysql from forms:

<?php   

//let's start our session, so we have access to stored data
session_start();

session_register('membership_type');
session_register('terms_and_conditions');

include 'db.inc.php';

$db = mysql_connect('localhost', 'root', '') or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db('ourgallery', $db) or die(mysql_error($db));
  

//let's create the query
$query = sprintf("INSERT INTO subscriptions (
name_id, name, email_address, membership_type)
VALUES ('%s','%s','%s','%s')",
name_id,
mysql_real_escape_string($_SESSION['name']),
mysql_real_escape_string($_SESSION['email_address']),
mysql_real_escape_string($_SESSION['membership_type']));

//let's run the query
$result = mysql_query($query, $db) or die(mysql_error($db));

$name_id = mysql_insert_id();

$query = sprintf("INSERT INTO site_user_info (
name_id, terms_and_conditions, name_on_card,
credit_card_number )
VALUES ('%s','%s','%s','%s')",
mysql_insert_id(),
mysql_real_escape_string($_SESSION['terms_and_conditions']),
mysql_real_escape_string($_POST['name_on_card']),
mysql_real_escape_string($_POST['credit_card_number']));

//let's run the query
$result = mysql_query($query, $db) or die(mysql_error($db));

$name_id = mysql_insert_id();

$query = sprintf("INSERT INTO card_numbers (
 name_id, credit_card_expiration_data)
VALUES ('%s','%s')",
mysql_insert_id(),
mysql_real_escape_string($_POST['credit_card_expiration_data']));

$result = mysql_query($query, $db) or die(mysql_error($db));
echo '$result';

?>


Should I be JOINING these tables together? I don't want the IDs to get muddled up otherwise it will screw up my database.

I have a table that contains the schools in my system:

schools
id
name
location
...

Then, I have three tables that use the id of this table:

schoolAdmins
schoolID

schoolContests
schoolID

students
schoolID

When I go to delete a school from my system, I want to check to see if that school is connected to any of these three other tables first.  This is what I tried (but obviously failed because I'm here) where I'm passing the query the $studentID in question:

SELECT * FROM schoolAdmins, schoolContests, students WHERE (schoolAdmins.schoolID = $schoolID) OR (schoolContests.schoolID = $schoolID) OR (students.schoolID = $schoolID)

I'm really new to the concept of querying multiple tables in a single statement, so I'm just kind of guessing at this point.  Thanks in advance.

How do I convert accented characters etc into their code form so I can put them into my database? I have tried everything I can think of like htmlentities, htmlspecialchars, url_decode etc and nothing seems to work!

I am storing content in a table with different fields for different languages, e.g. polish, spanish etc.
I can use the following function:

function getRewriteString($sString) {
     $string = strtolower(htmlentities($sString));
     $string = preg_replace("/&(.)(uml);/", "$1e", $string);
     $string = preg_replace("/&(.)(acute|cedil|circ|ring|tilde|uml);/", "$1", $string);
     $string = preg_replace("/([^a-z0-9]+)/", " ", html_entity_decode($string));
     $string = trim($string, "-");
     return $string;
  }

Which replaces accented chars with their unaccented counterparts but it doesn't work 100% of the time and isn't really what I want because I'd like to have the accented chars as they will be used on different language sites.

Thanks in advance.

I want to allow users to post entries to a NEWS table and, if they wish, to post an accompanying image to an IMAGES table.
Tables are like this:

NEWS
id  // if there'll be an accompanying image, this id to be sent to IMAGES table
title
subtitle
created
news_entry
category

IMAGES
image_id
f_news_id // the foreign id of the associated post in NEWS table
filename
caption
description

So, the user comes to the insert_entry.php page and creates a post. If the user clicks an "Upload accompanying image" link, the news post id must be inserted in the f_news_id field when the image is uploaded.

Code excerpt from insert_entry.php:
Code: [Select]
// Insert the news_entry in the database...
// Make the query:
$q = "INSERT INTO
news (title, subtitle, news_entry, category)
VALUES ('$title', '$subtitle', '$news_entry', '$category')
";
$r = @mysqli_query ($dbc, $q); // Run the query.
if ($r) { // If it ran OK.
// Print a message:
echo "<h1>Thank you!</h1>
<p>You have successfully inserted the News Entry below.</p>";
echo "<h1>" . stripslashes($title) . "</h1><h2>" . stripslashes($subtitle) . "</h2><p>" . stripslashes($news_entry) . "</p>";

// get id of record just created
$q = "SELECT id FROM news ORDER BY created DESC LIMIT 1";
$r = mysqli_query($dbc, $q);
while ($row = mysqli_fetch_assoc($r)) {
// pass the id via GET in the URL
echo "<a href='upload_image.php?=" . $row['id'] . "'>Upload image</a>";
}
mysqli_close($dbc);
?>
            

Code excerpt from upload_image.php:
Code: [Select]
// insert news post id into images table if user came via insert_entry.php page
// Make the query:
require_once ('includes/mysqli_connect.php'); // Connect to the db.
$description = mysqli_real_escape_string($dbc, trim($_POST['description']));
$caption = mysqli_real_escape_string($dbc, trim($_POST['caption']));
if (isset($_GET['id'])) { // if there's a NEWS post id
$q = "INSERT INTO
images (f_news_id, filename, caption, description)
VALUES ('$_GET['id']', '{$_FILES['upload']['name']}', '$caption', '$description')";
}
else
{ // if user arrived at upload_image.php otherwise and there's *not* a NEWS post id
$q = "INSERT INTO
images (filename, caption, description)
VALUES ('{$_FILES['upload']['name']}', '$caption', '$description')
";
}
$r = @mysqli_query ($dbc, $q); // Run the query.
if ($r) { // If it ran OK.

// Print a message:
echo "<p>Info entered in images table.</p>";

Am I going about this the wrong way? Am new to php... so any advice much appreciated...

Hi all

I have 2 tables:
1. is a tables full of images that have an id, a src and a title (images_table).
2. table is a list of records with an id, a description and an images_id (records_table)

What I'm looking to do is:

Loop through the records in the records_table and find the description, the description is something like this 'Welcome to Kansas'. Then, I want to loop through the images_table and find the associated image of Kansas, based on a MySQL LIKE statement.

This is because the images_table has a title of 'Kansas', for example. Once this has been done, I then need to insert the id of Kansas into the images_id in the records_table a possible MySQL UPDATE.

Has anyone got an idea how I could do this?

Thanks

I have a photo album style gallery to build
and
i'm finding it dificult to list all the table names (these are names of photo albums) and then enter the data into a seperate query for each album name (these will change often so i cant keep updating the file as normal. this will then post all the data to the xml file and show the set of photos in the individual albums in a flash file.
can anyone help me where im going wrong at all?



<?php
$dbname = 'cablard';

if (!mysql_connect('localhost', 'cablard', '')) {
    echo 'Could not connect to mysql';
    exit;
}

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
}

mysql_free_result($result);



$query = "SELECT * FROM photo ORDER BY id DESC"; 
$result2 = mysql_query ($query)  or die ("Error in query: $query. ".mysql_error());

   
   while ($row = mysql_fetch_array($result2))  
   {                                       
         echo  "
<image>
<date>".$row['date']."</date>
<title>".$row['title']."</title>
<desc>".$row['description']."</desc>
<thumb>".$row['thumb']."</thumb>
<img>".$row['image']."</img>
</image>

";


   }                      
?>


Thanks
James

CREATE TABLE posts (
postId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,	
title VARCHAR(255) NOT NULL,
author VARCHAR(24) NOT NULL,	
description TEXT NOT NULL,       
createdAt TIMESTAMP,
PRIMARY KEY (postId)    
);
CREATE TABLE comments(
commentId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,    
comment  TEXT NOT NULL,
postId INT(11),
userId INT(11),
createdAt TIMESTAMP,	
PRIMARY KEY (commentId),
FOREIGN KEY (userId) REFERENCES users(userId),
FOREIGN KEY (postId) REFERENCES posts(postId)   
);
CREATE TABLE replies (
repId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,   
reply  TEXT NOT NULL,
userId INT(11),
commentId INT(11), 	
createdAt TIMESTAMP,     
PRIMARY KEY (repId),
FOREIGN KEY (userId) REFERENCES users(userId),
FOREIGN KEY (commentId) REFERENCES comments(commentId)     
);
CREATE TABLE users (
userId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
userName VARCHAR(100) NOT NULL,,	          
email VARCHAR(100) NOT NULL,
PRIMARY KEY (userId)
);

how to retrive userName,comment, and createdAt from users and comments table while I have used userId as a  Foreign key on the comment table

if it isn't correct, correct me please

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

The following metadata...

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="NotionCommotion\App\Account\PublicAccountIdIncrementor" table="public_account_id_incrementor">
    <id name="account" association-key="true"/>
    <field name="chart_id" type="integer">
      <options>
        <option name="default">0</option>
      </options>
    </field>
    <field name="source_id" type="integer">
      <options>
        <option name="default">0</option>
      </options>
    </field>
    <one-to-one field="account" target-entity="NotionCommotion\App\Account\Account" fetch="LAZY" inversed-by="idPublicIncrementor">
      <join-columns>
        <join-column name="id" referenced-column-name="id" nullable="false"/>
      </join-columns>
    </one-to-one>
  </entity>
</doctrine-mapping>

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="NotionCommotion\App\Account\Account" table="account">
    <id name="id" type="integer"/>
    <one-to-one field="idPublicIncrementor" target-entity="NotionCommotion\App\Account\PublicAccountIdIncrementor" mapped-by="account"/>
  </entity>
</doctrine-mapping>

creates the following methods...

<?php
class Account
{
    /**
     * Set idPublicIncrementor.
     *
     * @param \NotionCommotion\App\Account\PublicAccountIdIncrementor|null $idPublicIncrementor
     *
     * @return Account
     */
    public function setIdPublicIncrementor(\NotionCommotion\App\Account\PublicAccountIdIncrementor $idPublicIncrementor = null)
    {
        $this->idPublicIncrementor = $idPublicIncrementor;

        return $this;
    }

    /**
     * Get idPublicIncrementor.
     *
     * @return \NotionCommotion\App\Account\PublicAccountIdIncrementor|null
     */
    public function getIdPublicIncrementor()
    {
        return $this->idPublicIncrementor;
    }
    
}

As seen, Account::idPublicIncrementor allows NULL value.  Is this by design since PublicAccountIdIncrementor's primary key is Account's primary key?  If not, how do I instruct Doctrine to prevent NULL?

just trying to see if i have this right in my head here..

if i have a page that has a topic on it and many replies,  the tables would look like this..


would this be the right code to pull out the topic AND all responses for say.
www.mysite.com/view?id=1

$topic = 'SELECT * from topics where id = $id'

$replies = 'SELECT * from replies where replies.topic_id = $id'

create table mimi (mimiId int(11) not null, mimiBody varchar(255) );
<?php
//connecting to database
include_once ('conn.php');
$sql ="SELECT mimiId, mimiBody FROM mimi";

$result = mysqli_query($conn, $sql );

$mimi = mysqli_fetch_assoc($result);

$mimiId ='<span>No: '.$mimi['mimiId'].'</span>';

$mimiBody ='<p class="leading text-justify">'.$mimi['mimiBody'].'</p>';
?>
//what is next?

i want to download pdf or text document after clicking button or link how to do that

I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player?

This is what I'm trying now:

Code: [Select]
/* CONNECTION VARIABLES */

$id = $_GET['id']; // get var from URL


/* Get data. */
$sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'";
$result = mysql_query($sql);

?>


<?php
$alternate = "2";
while ($row = mysql_fetch_array($result)) {
$field1 = $row["season"];
$field2 = $row["team"];
$field3 = $row["games"];
$field4 = $row["ave"];
$field5 = $row["slg"];
$field6 = $row["r"];
$field7 = $row["h"];
$field8 = $row["rbi"];
$field9 = $row["bb"];
$field10 = $row["k"];
$field11 = $row["hr"];
$field12 = $row["dbl"];
$field13 = $row["tpl"];
$field14 = $row["sb"];
$field15 = $row["obp"];
$field16 = $row["ops"];
if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#E4E4E4";
$alternate = "1";
}
echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>";
}
echo "</table>";
?>
Thank you in advance!

I'm creating a staff directory that I'd like to use alternating background colors (odd/even) to make it easier to read.  All of the information is stored in sql, which I can pull to the page with a repeat function, but I'm not sure how to get the zebra effect to alternate with the output.  Is there any easy way to do this?  I'm using CSS to change the background of the table itself.

Code: [Select]
<table width="100%" border="0" cellpadding="1" cellspacing="0" class="staff_b">
        <tr>
          <td width="35%"><div class="staff_h">Name</div></td>
          <td width="35%"><div class="staff_h">Department</div></td>
          <td width="30%"><div class="staff_h">Extension</div></td>
        </tr>
        <tr class="staff_e">
          <td>1</td>
          <td>2</td>
          <td>3</td>
        </tr>
        <tr class="staff_o">
          <td>4</td>
          <td>5</td>
          <td>6</td>
        </tr>
      </table>

I have two tables in which different information of members are stored...

table 1 stores the users main info, name, city, category, location

table 2 stores extra info such as sub categories of items they have.

Now i want to be able to search both tables for one value...say i type in red..i want to be able to search both tables at the same time to find the results and display them

So far i'm stuck in figuring out how to do this, which is why i'm asking for help now.

Hope someone can give me some guidance

I need some help on how to search in two different mysql tables, and then show the results sorted/ordered by the tables.

Quote

RESULTS:

Tabel 1
# ...
# ...

Tabel 2
# ...
# ...


Thanks in advance!