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.
[code=php]
$result = mysql_query("se lect 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>UserPasswor d</th>
<th>EncUserPass word</th>
<th>UserId</th>
<th>FirstName </th>
<th>UserId</th>
<th>Zip</th>
</tr>";

while ($row = mysql_fetch_arr ay($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($co n);[/code]

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

[code=mysql]
SET FOREIGN_KEY_CHE CKS=0;

DROP DATABASE IF EXISTS `userAccount`;

CREATE DATABASE `userAccount`
CHARACTER SET `utf8`
COLLATE `utf8_unicode_c i`;

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_unam e 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;
[/code]

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:
[code=mysql]
SELECT a.First, b.Second, c.Third FROM a, b, c[/code]
The results will not include the table name you used to identify the columns.
So to use the results of this query in PHP
[code=php]
// This is incorrect:
echo $resultRow['a.First'];

// It should be:
echo $resultRow['First'];[/code]

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("se lect 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>UserPasswor d</th>
<th>NameId</th>
<th>FirstName </th>
<th>ZipId</th>
<th>Zip</th>
</tr>";

while ($row = mysql_fetch_arr ay($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($co n);
?>
[/config.php]

[r035198x]

alright i made the changes as suggested but still this Error: Duplicate entry '1' for key 'PRIMARY'
...
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]

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_arr ay() 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("se lect a.user_Id as UserId,
a.UEA,
AES_DECRYPT(a.U EA, '$userpass') as UEAD,
a.UEAP,
AES_DECRYPT(a.U EAP, '$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.use r_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>UserPasswor d</th>
<th>EncUserPass word</th>
<th>NameId</th>
<th>FirstName </th>
<th>ZipId</th>
<th>Zip</th>
</tr>";

while ($row = mysql_fetch_arr ay($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($co n);
?>
[/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_exis ts("GetSQLValue String")) {
function GetSQLValueStri ng($theValue, $theType, $theDefinedValu e = "", $theNotDefinedV alue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quote s_gpc() ? stripslashes($t heValue) : $theValue;
}

$theValue = function_exists ("mysql_real_es cape_string") ? mysql_real_esca pe_string($theV alue) : mysql_escape_st ring($theValue) ;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValu e) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theV alue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValu e : $theNotDefinedV alue;
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)",
GetSQLValueStri ng($_POST['user_Id'], "int"),
GetSQLValueStri ng($_POST['UEA'], "text"),
GetSQLValueStri ng($_POST['UEAP'], "text"));

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

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

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

mysql_select_db ($database_unam e, $Schema);
$Result2 = mysql_query($in sertSQL1, $Schema) or die(mysql_error ());

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

$insertGoTo = "userdataset.ph p";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insert GoTo, '?')) ? "&" : "?";
$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.dt d">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitl ed Document</title>
</head>
<body>
<form action="<?php echo $editFormAction ; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baselin e">
<td nowrap="nowrap" align="right">E mail Address:</td>
<td><input type="text" name="UEA" value="" size="32" /></td>
</tr>
<tr valign="baselin e">
<td nowrap="nowrap" align="right">P assword:</td>
<td><input type="password" name="UEAP" value="" size="32" /></td>
</tr>
<tr valign="baselin e">
<td nowrap="nowrap" align="right">F irst Name:</td>
<td><input type="text" name="Fname" value="" size="32" /></td>
</tr>
<tr valign="baselin e">
<td nowrap="nowrap" align="right">L ast Name:</td>
<td><input type="text" name="Lname" value="" size="32" /></td>
</tr>
<tr valign="baselin e">
<td nowrap="nowrap" align="right">Z ip:</td>
<td><input type="text" name="Zip" value="" size="32" /></td>
</tr>
<tr valign="baselin e">
<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_f k" 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]