mysql_fetch_array() problem

[wizardry]

Good evening -

thanks in advance for you help!

attached is my query, and html table layout.

I'm trying to query 3 tbles in one select statement, and return the data to html table.

it work before when i had it broken down into three select statements and three results.

$result = mysql_query("select a.user_Id, AES_DECRYPT(`a.UEA`, '$userpass') as UEA, `a.UEA` as UEA1, AES_DECRYPT(`a.UEAP`, '$userpass') as UEAP, `a.UEAP` as UEAP1, b.user_Id_fk, b.Fname, c.user_Id_fk, c.Zip from user a, uname b, uzip c");
 
// create table layout
 
echo "<table border='1'>
<tr>
<th>UserId</th>
<th>UserName</th>
<th>EncUserName</th>
<th>UserPassword</th>
<th>EncUserPassword</th>
<th>UserId</th>
<th>FirstName</th>
<th>UserId</th>
<th>Zip</th>
</tr>";
 
while ($row = mysql_fetch_array($result))
{   
    echo "<tr>";
    echo "<td>" . $row ['a.user_Id'] . "</td> "; 
    echo "<td>" . $row ['UEA'] . "</td>";
    echo "<td>" . $row ['UEA1'] . "</td>";
    echo "<td>" . $row['UEAP'] . "</td>"; 
    echo "<td>" . $row['UEAP1'] . "</td>"; 
    echo "<td>" . $row ['b.user_Id_fk'] . "</td>"; 
    echo "<td>" . $row ['b.Fname'] . "</td>";
    echo "<td>" . $row ['c.user_Id_fk'] . "</td>";
    echo "<td>" . $row ['c.Zip'] . "</td>";
    echo "</tr>";
    //echo "<br />";
}
    echo "</table>";  
 
mysql_close($con);

[Atli]

Hi.

There is nothing essentially wrong with the query. It looks perfectly valid.

Your join syntax may be off tho. I can't really tell without knowing your table structures.

I suggest you read through 12.2.8.1. JOIN syntax in the reference manual.
It explains the various types of joins you can use. Perhaps you need to be using a different type.

[wizardry]

ok thanks will look into the tbl joins.

here is the table structure.

SET FOREIGN_KEY_CHECKS=0;
 
DROP DATABASE IF EXISTS `userAccount`;
 
CREATE DATABASE `userAccount`
CHARACTER SET `utf8`
COLLATE `utf8_unicode_ci`;
 
USE `userAccount`;
 
DROP TABLE IF EXISTS `user`;
 
CREATE TABLE `user` (
`user_Id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'user schema primary key',
`UEA` longblob COMMENT 'user account name email address',
`UEAP` longblob COMMENT 'user password',
PRIMARY KEY (`user_Id`)
) ENGINE = InnoDB;
 
 
DROP TABLE IF EXISTS `uname`;
 
CREATE TABLE `uname` (
`uname_Id` bigint UNSIGNED not null COMMENT 'user infomation schema primary key',
`user_Id_fk` bigint UNSIGNED auto_increment COMMENT 'users admin master primary key',
`Fname` varchar(50) COMMENT 'user first name',
`Lname` varchar(50) COMMENT 'user last name',
PRIMARY KEY (`user_Id_fk`, `uname_Id`),
constraint user_Id_fk_uname foreign key (`user_Id_fk`)
references `user`(`user_Id`)
on update cascade
on delete cascade
) ENGINE = InnoDB;
 
DROP TABLE IF EXISTS `uzip`;
 
CREATE TABLE `uzip` (
`user_Id_fk` bigint UNSIGNED auto_increment COMMENT 'user schema foreign key',
`uzip_Id` bigint UNSIGNED COMMENT 'user zip primary key',
`Zip` varchar(17) COMMENT 'user zip',
PRIMARY KEY (`user_Id_fk`),
constraint user_Id_fk_uzip foreign key(`user_Id_fk`)
references `user`(`user_Id`)
on update cascade
on delete cascade
) ENGINE = InnoDB;
 

thanks in advance for you help.

[wizardry]

this is the error that i get when i placed the echo . error

codeError: Duplicate entry '22' for key 'PRIMARY'

i run a query in sqlplus and it returns 22 records.

this worked fine when i used a single select for each table. now that i joined them and want to layout the data in html table it errors.

[Atli]

Hmm... that's a weird error to get for a SELECT query.
That is something I would expect to get from a INSERT or a UPDATE query, not a SELECT query. Are you sure this is correct?

In any case, two things came to my attention when I looked closer at your code.

When you do:

SELECT a.First, b.Second, c.Third FROM a, b, c

The results will not include the table name you used to identify the columns.
So to use the results of this query in PHP

// This is incorrect:
echo $resultRow['a.First'];
 
// It should be:
echo $resultRow['First'];

And, because of this, it is not a good idea to have two columns share a name. Two elements can't share a name in a PHP array, so one of them would never make it to your PHP result array.

So, you should create aliases for the 'user_Id_fk' fields in your query. That way you can be sure you are reading them correctly in your PHP code.

[wizardry]

thanks you, i will look into it. I ran the query fine in sqlplus on the database with the exception not being able to decrypt.

i will rename the columns i have tags for in php. Thanks i just assumed it was reading the array, and the objects in the array not just the field name.

i will repost once i've tested.

[wizardry]

alright i made the changes as suggested but still this Error: Duplicate entry '1' for key 'PRIMARY'

I'm mainly oracle/sql server dba not apps

thanks again for your help in advance.

here is my code in the page that is erroring out.

i've copied the last inserted id to the 2 child tables to accomidate the matching puzzle piece. also i've tried throwing nulls at the auto_inc but that just errored out.


[config.php]
$sql=("insert into user ( user_Id, UEA, UEAP) values (null, AES_ENCRYPT('$_POST[username]', '$userpass'), AES_ENCRYPT('$_POST[password]', '$userpass'))");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

echo " 1 Record added in users account schema";
//uname table
$sql=("insert into uname ( user_Id_fk, uname_Id, Fname, Lname) values ( LAST_INSERT_ID(), LAST_INSERT_ID(), '$_POST[firstname]', '$_POST[lastname]')");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo " 1 Record added users name schema";
// zip code table
$sql=("insert into uzip ( user_Id_fk, uzip_Id, Zip ) values (LAST_INSERT_ID(), LAST_INSERT_ID(), '$_POST[zip]')");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

echo " 1 Record added to users schema zip code";
//*
$result = mysql_query("select a.user_Id as UserId,
a.UEA,
a.UEAP,
b.user_Id_fk as NameId, b.Fname,
c.user_Id_fk as ZipId, c.Zip
from
user a,
uname b,
uzip c
where
UserId=NameId and ZipId=UserId)");

if (!mysql_query($sql,$con))
{
die(' Error: ' . mysql_error());
}


// create table layout

echo "<table border='1'>
<tr>
<th>UserId</th>
<th>UserName</th>
<th>UserPassword</th>
<th>NameId</th>
<th>FirstName</th>
<th>ZipId</th>
<th>Zip</th>
</tr>";

while ($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row ['UserId'] . "</td> ";
echo "<td>" . $row ['UEA'] . "</td>";
echo "<td>" . $row['UEAP'] . "</td>";
echo "<td>" . $row ['NameId'] . "</td>";
echo "<td>" . $row ['Fname'] . "</td>";
echo "<td>" . $row ['ZipId'] . "</td>";
echo "<td>" . $row ['Zip'] . "</td>";
echo "</tr>";

}
echo "</table>";
//*/
mysql_close($con);
?>
[/config.php]

[r035198x]

@wizardry
After copying the las tinsert id to the first child table, the id of that child table becomes the new last insert id. Are you sure that is the id that you want to copy to the second child table?

[wizardry]

yes thats what i want to add because it grabs the parent id and inserts in into the child records per that client connection. if another connection was to run it would have its own process thread.

everything worked fine until i changed the way i wanted to layout the return form.

i had 3 seperate results and spread the data out into html tbl i like the way this table will look.

but any how

it does insert the data it just errors and doesn't return the data.

[wizardry]

ok i took out a error handler and after the $results query and this is the actual error. mysql_fetch_array() supplied argument is not a valid MySQL

so it still relates back to the query.

i dont understand this the query im running now runs fine on the sqlplus but not in php?

thanks in advance for your help

[r035198x]

Echo out the sql that is being executed and post it here. When you said the last_insert_id() are inserting fine, do you mean that you have say a parent table P, a child table A (child of P) and another child table B which is a child of A rather than a child of P? Because that is what your inserts will do.

[wizardry]

ok, here is the schema!

parent a 1=> 1 child b and child c 1 => 1 to a

A
/----\
B-----C

these are a required data tables i could use use c 1=> 1 to b

[wizardry]

it was the results query i needed to change a couple single quotes to back slash.

here is the completed results.

[php]
$sql=("insert into user ( user_Id, UEA, UEAP) values (null, AES_ENCRYPT('$_POST[username]', '$userpass'), AES_ENCRYPT('$_POST[password]', '$userpass'))");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

echo " 1 Record added in users account schema";
//uname table
$sql=("insert into uname ( user_Id_fk, uname_Id, Fname, Lname) values ( LAST_INSERT_ID(), LAST_INSERT_ID(), '$_POST[firstname]', '$_POST[lastname]')");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo " 1 Record added users name schema";
// zip code table
$sql=("insert into uzip ( user_Id_fk, uzip_Id, Zip ) values (LAST_INSERT_ID(), LAST_INSERT_ID(), '$_POST[zip]')");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

echo " 1 Record added to users schema zip code";
//*
$result = mysql_query("select a.user_Id as UserId,
a.UEA,
AES_DECRYPT(a.UEA, '$userpass') as UEAD,
a.UEAP,
AES_DECRYPT(a.UEAP, '$userpass') as UEAPD,
b.user_Id_fk as NameId,
b.Fname,
c.user_Id_fk as ZipId,
c.Zip
from
user a,
uname b,
uzip c
where a.user_Id=b.user_Id_fk and c.user_Id_fk=a.user_Id");

// create table layout
//where 'a.user_Id'='b.uname_Id_fk' and 'c.uzip_Id'='a.user_Id'
echo "<table border='1'>
<tr>
<th>UserId</th>
<th>UserName</th>
<th>EncUserName</th>
<th>UserPassword</th>
<th>EncUserPassword</th>
<th>NameId</th>
<th>FirstName</th>
<th>ZipId</th>
<th>Zip</th>
</tr>";

while ($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row ['UserId'] . "</td> ";
echo "<td>" . $row ['UEA'] . "</td>";
echo "<td>" . $row ['UEAD'] . "</td>";
echo "<td>" . $row ['UEAP'] . "</td>";
echo "<td>" . $row ['UEAPD'] . "</td>";
echo "<td>" . $row ['NameId'] . "</td>";
echo "<td>" . $row ['Fname'] . "</td>";
echo "<td>" . $row ['ZipId'] . "</td>";
echo "<td>" . $row ['Zip'] . "</td>";
echo "</tr>";

}
echo "</table>";
//*/
mysql_close($con);
?>
[/php]

thanks, again for your help!

[wizardry]

hello again - some what a same problem. didn't want to start a new thread.

i'm using dw for the designer. and i'm tring to recreate what i did in the last post; using their auto forms wizard.

i've changed the connections.php to reflect what database its going to.

here is the error: Table 'uname.user' doesn't exist

I thought another set of eyes could help! thanks in advance for you help!

here is the code:
[php]
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO user (user_Id, UEA, UEAP) VALUES (null, %s, %s)",
GetSQLValueString($_POST['user_Id'], "int"),
GetSQLValueString($_POST['UEA'], "text"),
GetSQLValueString($_POST['UEAP'], "text"));

$insertSQL1 = sprintf("insert into uname (uname_Id, user_Id_fk, Fname, Lname) values ( LAST_INSERT_ID(), LAST_INSERT_ID(), %s, %s )", GetSQLValueString($_POST['uname_Id'], "int"),
GetSQLValueString($_POST['user_Id_fk'], "int"),
GetSQLValueString($_POST['Fname'], "text"),
GetSQLValueString($_POST['Lname'], "text"));

$insertSQL2 = sprintf("insert into uzip (uname_Id_fk, uzip_Id, Zip) VALUES (LAST_INSERT_ID(), null, %s,)", GetSQLValueString($_POST['uname_Id_fk'], "int"),
GetSQLValueString($_POST['uzip_Id'], "int"),
GetSQLValueString($_POST['Zip'], "text"));

//----------------- db name ------------- db connection
mysql_select_db($database_user, $Schema);
$Result1 = mysql_query($insertSQL, $Schema) or die(mysql_error());

mysql_select_db($database_uname, $Schema);
$Result2 = mysql_query($insertSQL1, $Schema) or die(mysql_error());

mysql_select_db($database_uzip, $Schema);
$Result3 = mysql_query($insertSQL2, $Schema) or die(mysql_error());

$insertGoTo = "userdataset.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
?>
<!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 http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">Email Address:</td>
<td><input type="text" name="UEA" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Password:</td>
<td><input type="password" name="UEAP" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">First Name:</td>
<td><input type="text" name="Fname" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Last Name:</td>
<td><input type="text" name="Lname" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Zip:</td>
<td><input type="text" name="Zip" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">&nbsp;</td>
<td><input type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="user_Id" value="" />
<input type="hidden" name="uname_Id" value="" />
<input type="hidden" name="user_Id_fk" value="" />
<input type="hidden" name="uname_Id_fk" value="" />
<input type="hidden" name="uzip_Id" value="" />
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p>&nbsp;</p>
</body>
</html>
[/php]