PHP - Foreign Characters With Php And Mysql

I have one mysql table with data and accessing this data through one php script. This works fine in REDHAT 8 linux. I have shifted mysql database and the php script to new server with REDHAT RHEL4.4. now when I execute the script from website, it displays junk character (reading form mysql database through php script). There is no error in mysql database/table and script is working fine in redhat 8. I have checked php.ini for default characterset as well as set the encoding in mysql as latin1. Where else should I look/change? any idea?

Hello,

I posted here as although it's in regards to mysql the thing i want to ask is in regards to php itself.

I have two tables below as follows:

Code: [Select]
    CREATE TABLE `users` (
    `uid` int(11) NOT NULL AUTO_INCREMENT,
    `status` char(10) NOT NULL,
    `username` varchar(15) NOT NULL,
    `email` varchar(50) NOT NULL,
    `password` char(32) NOT NULL,
    `reg_date` int(11) NOT NULL,
    `ip` varchar(39) DEFAULT NULL,
    PRIMARY KEY (`uid`),
    UNIQUE KEY `username` (`username`,`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
     
    CREATE TABLE `users_profiles` (
    `uid` int(11) NOT NULL,
    `first_name` varchar(40) DEFAULT NULL,
    `last_name` varchar(50) DEFAULT NULL,
    `gender` char(6) DEFAULT NULL,
    `website` varchar(100) DEFAULT NULL,
    `msn` varchar(60) DEFAULT NULL,
    `aim` varchar(60) DEFAULT NULL,
    `yim` varchar(60) DEFAULT NULL,
    `twitter` varchar(15) DEFAULT NULL,
    UNIQUE KEY `uid` (`uid`),
    CONSTRAINT `users_profiles_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

From the above you can see the uid in the users_profiles table is a foreign key and references the uid in the users table to link both tables together.

My scenario is this:

A user fills out a registration form that asks for username, email and a password. After validating the form data etc it is inserted into the users table.

Now at this stage they have no profile in the users_profiles table as they only just signed up. They login and xxx data is stored in a session along with there uid from users table. They now visit there profile, they fill in the profile form fields and submit the form.

Now can someone tell me if what i think i should be doing is correct, if not please tell me what i should do or a better way:

After validating the data that was submitted on profile form etc i could do something like a simple if else statement which will first check to see if the uid in session which is from users table matched a uid in the users_profiles table. If there's no match which in this case it won't be as they are a new user and don't have a profile record in users_profiles table then do an INSERT query into users_profiles table which would insert the submitted values from the form and the uid from session, on success that would now link the user from users table to there newly created profile in the users_profiles table. But if the uid in session matched a uid in user_profiles then obviously this means the user already has a profile ie.. submitted profile info previously so do an UPDATE query instead.

Am i rite in thinking this is how i would do it?

example below, note it's not valid code just to try an illustrate what i am saying; Hopefully!

Code: [Select]
<?php
    if($_SESSION['uid'] == user_profiles.uid)
    {
     /* If the uid in session which is the uid from users table
      * (we got it on successful login)
      * matches a users uid in user_profiles table then profile already exists
      * for user so do an UPDATE query here
      */
    } else {
     /* $_SESSION['uid'] does not match a uid in users_profiles table
      * so instead do a INSERT QUERY here. This means no profile exists for user.
      * The uid from session is inserted into users_profiles table (column uid)
      * which links the profile to user.
      */
    }
?>

I know if i delete a user it will also delete there profile if they have one like it should do to obviously not leave redundant data in the database and to ensure data intergity (sorry i think that's what it's called)

Thanks for any help.

PHPFAN

Hi,

I'm having following problem when I try to add records in two tables, one (password) has primary key and student has a foreign key contraint with password.

Anyone please clarify what Im doing wrong.

Thanks !

SQL CODE:

INSERT INTO student(id, firstname, surname,dob, class,nationality,houseno,line1,line2,city)
  VALUES('00010', 'test','test','1980-01-01','test','test','test','test','test','test');

INSERT INTO password (id, password,salt)
  VALUES('00010','abc','abcde');

ERROR:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`chr_fyp/student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`id`) REFERENCES `password` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

I have a table that holds a set of matches in a football (soccer) league. On a teams individual page I want to show just their matches and am very close to getting it right but just cannot get there!

I am using 2 tables for this....
teams
team_id team_name 1 TEAM1 2 TEAM2 3 TEAM3 4 TEAM4
and

all_games game_id home_team home_score away_team away_score 1 1 2 2 0 2 3 0 4 1 3 4 2 1 1 4 2 0 3 0 Both 'home_team' and 'away_team' are foreign keys to teams.team_id.

The query that I have used to almost get it working is
SELECT * FROM  all_games, teams WHERE '%s' IN (home_team, away_team )  AND all_games.home_team=teams.team_id

And the html is
        Code: [Select]
   
<tr>
        <td>all_games_id</td>
        <td>home_team</td>
        <td>home_goals</td>
        <td>away_team</td>
        <td>away_goals</td>
</tr>
<tr>
        <td><?php echo $row_all_games['all_games_id']; ?></td>
        <td><?php echo $row_all_games['team_name']; ?></td>
        <td><?php echo $row_all_games['home_goals']; ?></td>
        <td><?php echo $row_all_games['team_name']; ?></td>
        <td><?php echo $row_all_games['away_goals']; ?></td>
        </tr>     
     
This pulls the correct games into the page but will show the 'home_team.team_name' as both 'home_team' and 'away_team'. For example the page for TEAM1 shows up as
match_id home_team home_score away_team away_score 1 TEAM1 2 TEAM1 0 3 TEAM4 2 TEAM4 1
What it should show is
match_id home_team home_score away_team away_score 1 TEAM1 2 TEAM2 0 3 TEAM4 2 TEAM1 1
Obviously in the current query there is nothing to show that the 'away_team' is also equal to 'teams.team_id'. I have tried this query
SELECT * FROM  all_games, teams WHERE '%s' IN (home_team, away_team )  AND (all_games.home_team=teams.team_id OR all_games.away_team=teams.team_id)
But this gives the exact opposite result with just the 'away_team.team_name' showing up as 'home_team' and 'away_team'
match_id home_team home_score away_team away_score 1 TEAM2 2 TEAM2 0 3 TEAM1 2 TEAM1 1
while

SELECT * FROM  all_games, teams WHERE '%s' IN (home_team, away_team )  AND (all_games.home_team=teams.team_id AND all_games.away_team=teams.team_id)
throws up an empty query.

Does anyone have any suggestions on how this can be done? I have exhausted my patience with Google simply as I do not know what the correct search term is.


As an aside, when I do get it working, I want the format on the page to be slightly different in that instead of showing both home_team and away_team names I would want it to show "H" if the team in question is the 'home_team' and "A" if it is the 'away_ team' plus the name of the team that they played and whether the 'home_score' or 'away_score' belonged to them, as well as whether they won, drew or lost the game. So, staying with the page for TEAM1 it would show as
MATCH ID HOME or AWAY OPPONENT W/D/L SCORE FOR SCORE AGAINST 1 H TEAM2 Won 2 0 3 A TEAM4 Lost 1 2
I am currently planning on doing this with IF statements in the html, something along these lines if home_team = %s echo "Home" else echo "Away"
and
if home_team = %s AND home_score>away_score echo "Won"
else if away_team = %s and away_score>home_score echo "Won"
else if home_score= away_score echo "Drew"
else echo "Lost

If anyone can think of a better way to do this ( I am naturally assuming that it would not work in the way I intend!)I would be extremely greatful too!

Thanks in advance for any advice, pointers or offers to write the whole code!!!
Steve

Would someone tell me how to allow foreign letters, normal english letters and numbers using the preg_replace php function?

I've hit a bit of a roadblock with one of my projects and I cant seem to get past it. I am trying to add a new row to one of my tables, but the foreign key seems to be causing me problems. As you will see from my code below, I am trying to insert a row into my 'job' table.

Error:
Query failed: Cannot add or update a child row: a foreign key constraint fails (`kanix_support_desk`.`job`, CONSTRAINT `job_ibfk_1` FOREIGN KEY (`agentId`) REFERENCES `agents` (`agentId`))

Tables:
Code: [Select]
CREATE TABLE IF NOT EXISTS `agents` (
  `agentId` int(11) NOT NULL AUTO_INCREMENT,
  `AgentFirstname` varchar(50) DEFAULT NULL,
  `AgentSurname` varchar(50) DEFAULT NULL,
  `username` varchar(25) DEFAULT NULL,
  `password` varchar(25) DEFAULT NULL,
  `rank` varchar(12) NOT NULL,
  PRIMARY KEY (`agentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `job` (
  `jobId` int(11) NOT NULL AUTO_INCREMENT,
  `agentId` int(11) NOT NULL,
  `date` date NOT NULL,
  `time` time DEFAULT NULL,
  `jobName` varchar(100) NOT NULL,
  `jobDescription` varchar(9999) NOT NULL,
  `jobstatus` varchar(50) NOT NULL,
  `customerFirstname` varchar(50) DEFAULT NULL,
  `customerSurname` varchar(50) DEFAULT NULL,
  `CustomerTelephoneNo` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`jobId`),
  KEY `agentId` (`agentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Php:
Code: [Select]
session_start();

include("dbConnect.php");

$agent = $_SESSION['agent']; //Set agent name and ID variables
$agentid = $_SESSION['identity'];

//Check that no section of the form was left blank
if($_POST['fname'] != null && $_POST['sname'] != null && $_POST['phone'] != null && $_POST['jtitle'] != null && $_POST['jdesc'] != null){

mysql_query("INSERT INTO job (jobId, agentId, date, time, jobName, jobDescription, jobstatus, customerFirstname, customerSurname, customerTelephoneNo) VALUES (null, '".$agentid."', CURDATE(), NOW(), '".$_POST['jtitle']."', '".$_POST['jdesc']."', 'Live', '".$_POST['fname']."', '".$_POST['sname']."', '".$_POST['phone']."')") or die ("Query failed: " . mysql_error());


header("location:reception.php?content=3");


} else{ //Error message
header("location:reception.php?content=4");
}

I have two tables in my MySQL database:

1. user
user_id | firstname | lastname | nickname etc.

and

2. con (for contribution)
con_id | user_id (foreign key) | name | contribution | category etc.


Here's how I wanted to solve the problem of assigning the foreign key to the 2nd table (I'm a beginner so bear with me. )

When logging in, I assigned the user_id to the session variable like this:

// do the while loop

while ($assoc = mysqli_fetch_assoc($rows)) {

// assign database COLUMNS to the variables

$dbuser_id = $assoc['user_id'];

$dbuser_name = $assoc['nickname'];

$dbuser_password = $assoc['password'];

etc.....................


// set a session after login

$_SESSION['user'] = $dbuser_name . $dbuser_id;



This prints out the user_id just like I wanted to:


echo "Your user_id is: " . $_SESSION['user_id'] = $dbuser_id;


Gives:
Code: [Select]
Your user_id is: 35


... and this is how the assignment of the foreign key looks like WHILE posting the contribution:


 $user_id = $_SESSION['user'] = $dbuser_id;

  if (!empty($knuffix_name) && !empty($knuffix_category) && !empty($knuffix_contribution)) {
      // Write the data into the database
      $query = sprintf("INSERT INTO con (con_id, user_id, name, contribution, category, contributed_date) VALUES (' ', '$user_id', '%s', '%s', '%s', now())",

mysqli_real_escape_string($dbc, $knuffix_name),

mysqli_real_escape_string($dbc, $knuffix_contribution),

mysqli_real_escape_string($dbc, $knuffix_category));



When I now do a contribution through the input areas, nothing gets inserted into the database and I automatically get logged out. Obviously it's not working as I thought it would and I'd like to know why is that?
Notice that if I take the user_id part OUT, it's working again, so the rest of the code must be right then.

Another question I have is:
Is this common practice to solve this problem of assigning a foreign key, or is there a better way of doing it?

Hi, I cannot find where to add foreign key constraints in phpmyadmin!

Any help is appreciated!

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...

This topic has been moved to PHP Applications.

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

For example:
Code: [Select]
DELETE FROM user_question_answer WHERE user_id = 1 AND question_id = 7 AND `answer_id` = 3
This sql query has no error, but reports deleting 0 rows.

user_id and question_id fields are both foreign keys in this table.  Could that be why it's not working, yet not reporting an error?

Thank you for your time.

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.