Lettura dati Mysql da due tabelle diverse

[dino1957]

Salve!
Sto cercando di modificare questo file php inserendo il valore punti che si trova in un'altra tabella mysql.
Mi succede però che in ogni pagina di 25 campi il valore viene preso per il primo campo e ripetuto in tutti i 24 campi successivi.
Sapete darmi un mano a risolvere il problema?
Grazie mille anticipate
Dino



<?php require_once('Connections/dbConnect.php'); ?>
<?php
$currentPage = $HTTP_SERVER_VARS["PHP_SELF"];

$maxRows_userList = 25;
$pageNum_userList = 0;

if (isset($HTTP_GET_VARS['pageNum_userList'])) {
$pageNum_userList = $HTTP_GET_VARS['pageNum_userList'];
}
$startRow_userList = $pageNum_userList * $maxRows_userList;

mysql_select_db($database_dbConnect, $dbConnect);
$query_userList = "select * from user order by id";
$query_limit_userList = sprintf("%s LIMIT %d, %d", $query_userList, $startRow_userList, $maxRows_userList);
$userList = mysql_query($query_limit_userList, $dbConnect) or die(mysql_error());
$row_userList = mysql_fetch_assoc($userList);

//////////NUOVA STRINGA PUNTI/////
if (isset($HTTP_GET_VARS['pageNum_userList'])) {
$pageNum_userList = $HTTP_GET_VARS['pageNum_userList'];
}
$startRow_userList1 = $pageNum_userList * $maxRows_userList;

mysql_select_db($database_dbConnect, $dbConnect);
$query_userList1 = "select * from points order by userid";
$query_limit_userList1 = sprintf("%s LIMIT %d, %d", $query_userList1, $startRow_userList1, $maxRows_userList);
$userList1 = mysql_query($query_limit_userList1, $dbConnect) or die(mysql_error());
$row_userList1 = mysql_fetch_assoc($userList1);

////////FINE NUOVA STRINGA//////

if (isset($HTTP_GET_VARS['totalRows_userList'])) {
$totalRows_userList = $HTTP_GET_VARS['totalRows_userList'];
} else {
$all_userList = mysql_query($query_userList);
$totalRows_userList = mysql_num_rows($all_userList);
}
$totalPages_userList = ceil($totalRows_userList/$maxRows_userList)-1;

$queryString_userList = "";
if (!empty($HTTP_SERVER_VARS['QUERY_STRING'])) {
$params = explode("&", $HTTP_SERVER_VARS['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_userList") == false &&
stristr($param, "totalRows_userList") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_userList = "&" . implode("&", $newParams);
}
}
$queryString_userList = sprintf("&totalRows_userList=%d%s", $totalRows_userList, $queryString_userList);

?>
<span class="smallstatsText">Elenco da <?php echo ($startRow_userList + 1) ?> a <?php echo min($startRow_userList + $maxRows_userList, $totalRows_userList) ?> di <?php echo $totalRows_userList ?> </span>

<table width="100%" border="0" cellspacing="0" cellpadding="2" class="supermenuNoshadow">
<tr>
<td width="8%" class="menuheader">user id</td>
<td width="12%" class="menuheader">username</td>
<td width="14%" class="menuheader">password</td>
<td width="24%" align="left" class="menuheader">email</td>
<td width="10%" align="center" class="menuheader">incremento</td>
<td width="10%" align="center" class="menuheader">punti</td>
<td width="12%" align="center" class="menuheader">attiva?</td>
<td width="10%" align="center" class="menuheaderAction">azione</td>
</tr>
<?php do { ?>
<tr>
<td class="smallstatsText"><?php echo $row_userList['id']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['username']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['password']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['email']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList['point_inc']; ?></td>
////////////////INSERIMENTO NUOVO VALORE////////
<td align="center" class="smallstatsText"><?php echo $row_userList1['points']; ?></td>
////////////////FINE INSERIMENTO NUOVO VALORE////////
<td align="center" class="smallstatsText"><?php echo $row_userList['verified']; ?></td>
<td align="center">[img]images/edit.gif[/img]
| [img]images/delete.gif[/img]</td>
</tr>
<?php } while ($row_userList = mysql_fetch_assoc($userList)); ?>

<tr>
<td height="15" colspan="6" align="center" >">[img]images/first.gif[/img]
">[img]images/prev.gif[/img]
">[img]images/next.gif[/img]
">[img]images/last.gif[/img]</td>
</tr>
</table>
<?php
mysql_free_result($userList);

?>

[ciro78]

posta la struttura delle due tabelle

[softhare]

Non funziona perchè è sbagliato.

La linea:

<?php } while ($row_userList = mysql_fetch_assoc($userList)); ?>

Aggiorna ad ogni ciclo il contenuto dell'array associativo $row_userList

Ma poche linee prima, dove invii il punteggio, usi:

<?php echo $row_userList1['points']; ?>

Ma l'array $row_userList1 non viene aggiornato ad ogni ciclo, e fornisce sempre lo stesso valore.

Lo script andrebbe riscritto...

[dino1957]

Questa è la struttura delle due tabelle

TABLE user
Field Type Null Key Default Extra
id int(5) PRI auto_increment
fname varchar(36)
lname varchar(36)
username varchar(10) UNI
password varchar(10)
email varchar(255)
acct_type int(2) 1
point_inc decimal(3,1) 0.0
referral varchar(10)
joindate timestamp YES CURRENT_TIMESTAMP
verified char(1) YES n
receiveEmail char(1) y


TABLE points
Field Type Null Key Default Extra
userid int(5) PRI 0
username varchar(10) points decimal(7,1) 0.0

[dino1957]

in merito al consiglio di softhare:
potresti essere più dettagliato, quale parte dello script dovrei riscrivere?
Mi puoi dare una dritta?
Grazie mille
Dino

[dino1957]

Ho comunque scoperto che se do il valore

$maxRows_userList = 1;
anzichè
$maxRows_userList = 25;

le pagine avendo un solo campo mi restituiscono il valore dei punti in maniera corretta in tutte le pagine,
ma se le pagine hanno ad esempio 20 campi il volore dei punti viene restituito corretto per il primo campo della pagina e ripetuto per gli altri 19 e così via nelle successive pagine

[softhare]

Perchè funzioni dovrebbe bastare modificare la linea

<?php } while ($row_userList = mysql_fetch_assoc($userList)); ?>

con quest'altra:

<?php } while (($row_userList = mysql_fetch_assoc($userList))&&($row_userList1 = mysql_fetch_assoc($userList1)); ?>

Se non funziona così, prova ad usare or ( || ) al posto di and ( && ) .

Ma lo script non è ben strutturato: usa molte più operazioni e variabili di quelle necessarie, riusa le stesse variabili per diversi scopi, duplica porzioni non necessarie.
Per questo oltre a presentare l'errore segnalato era difficile trovare di cosa si trattava.
Fosse per me lo riscriverei da capo semplificandolo.

[dino1957]

x Softhare
Ho provato a riscrivere lo script eliminando le ripetizioni e ho inserito il tuo suggerimento.
Adesso i punti vengono conteggiati regolarmente in tutti i campi, ma invece gli altri dati non vengono richiamati
Allego script e immagine pagina web


<?php require_once('Connections/dbConnect.php'); ?>
<?php
$currentPage = $HTTP_SERVER_VARS["PHP_SELF"];

$maxRows_userList = 25;
$pageNum_userList = 0;

if (isset($HTTP_GET_VARS['pageNum_userList'])) {
$pageNum_userList = $HTTP_GET_VARS['pageNum_userList'];
}
$startRow_userList = $pageNum_userList * $maxRows_userList;

mysql_select_db($database_dbConnect, $dbConnect);
$query_userList = "select * from user order by id";
$query_limit_userList = sprintf("%s LIMIT %d, %d", $query_userList, $startRow_userList, $maxRows_userList);
$userList = mysql_query($query_limit_userList, $dbConnect) or die(mysql_error());
$row_userList = mysql_fetch_assoc($userList);

//////////NUOVA STRINGA PUNTI/////

$query_userList1 = "select * from points order by userid";
$query_limit_userList1 = sprintf("%s LIMIT %d, %d", $query_userList1, $startRow_userList1, $maxRows_userList);
$userList1 = mysql_query($query_limit_userList1, $dbConnect) or die(mysql_error());
$row_userList1 = mysql_fetch_assoc($userList1);

////////FINE NUOVA STRINGA//////

if (isset($HTTP_GET_VARS['totalRows_userList'])) {
$totalRows_userList = $HTTP_GET_VARS['totalRows_userList'];
} else {
$all_userList = mysql_query($query_userList);
$totalRows_userList = mysql_num_rows($all_userList);
}
$totalPages_userList = ceil($totalRows_userList/$maxRows_userList)-1;

$queryString_userList = "";
if (!empty($HTTP_SERVER_VARS['QUERY_STRING'])) {
$params = explode("&", $HTTP_SERVER_VARS['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_userList") == false &&
stristr($param, "totalRows_userList") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_userList = "&" . implode("&", $newParams);
}
}
$queryString_userList = sprintf("&totalRows_userList=%d%s", $totalRows_userList, $queryString_userList);

?>
<span class="smallstatsText">Elenco da <?php echo ($startRow_userList + 1) ?> a <?php echo min($startRow_userList + $maxRows_userList, $totalRows_userList) ?> di <?php echo $totalRows_userList ?> </span>

<table width="100%" border="0" cellspacing="0" cellpadding="2" class="supermenuNoshadow">
<tr>
<td width="8%" class="menuheader">user id</td>
<td width="12%" class="menuheader">username</td>
<td width="14%" class="menuheader">password</td>
<td width="24%" align="left" class="menuheader">email</td>
<td width="10%" align="center" class="menuheader">incremento</td>
<td width="10%" align="center" class="menuheader">punti</td>
<td width="12%" align="center" class="menuheader">attiva?</td>
<td width="10%" align="center" class="menuheaderAction">azione</td>
</tr>
<?php do { ?>
<tr>
<td class="smallstatsText"><?php echo $row_userList['id']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['username']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['password']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['email']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList['point_inc']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList1['points']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList['verified']; ?></td>
<td align="center">[img]images/edit.gif[/img]
| [img]images/delete.gif[/img]</td>
</tr>
<?php } while ($row_userList = mysql_fetch_assoc($userList)&& $row_userList1 = mysql_fetch_assoc($userList1));
?>


<tr>
<td height="15" colspan="6" align="center" >">[img]images/first.gif[/img]
">[img]images/prev.gif[/img]
">[img]images/next.gif[/img]
">[img]images/last.gif[/img]</td>
</tr>
</table>
<?php
mysql_free_result($userList);

?>

[dino1957]

Immagine non caricata precedentemente

[dino1957]

Risolto!!! Ho dovuto modificare alcuni dati e sostituire && con and.
T ringrazio Softhare per avermi dato l'input da dove iniziare.
ecco lo script corretto:

<?php require_once('Connections/dbConnect.php'); ?>
<?php
$currentPage = $HTTP_SERVER_VARS["PHP_SELF"];

$maxRows_userList = 50;
$pageNum_userList = 0;

if (isset($HTTP_GET_VARS['pageNum_userList'])) {
$pageNum_userList = $HTTP_GET_VARS['pageNum_userList'];
}
$startRow_userList = $pageNum_userList * $maxRows_userList;

mysql_select_db($database_dbConnect, $dbConnect);
$query_userList = "select * from user order by id";
$query_limit_userList = sprintf("%s LIMIT %d, %d", $query_userList, $startRow_userList, $maxRows_userList);
$userList = mysql_query($query_limit_userList, $dbConnect) or die(mysql_error());
$row_userList = mysql_fetch_assoc($userList);

//////////NUOVA STRINGA PUNTI/////

$query_userList1 = "select * from points order by userid";
$query_limit_userList1 = sprintf("%s LIMIT %d, %d", $query_userList1, $startRow_userList, $maxRows_userList);
$userList1 = mysql_query($query_limit_userList1, $dbConnect) or die(mysql_error());
$row_userList1 = mysql_fetch_assoc($userList1);

////////FINE NUOVA STRINGA//////

if (isset($HTTP_GET_VARS['totalRows_userList'])) {
$totalRows_userList = $HTTP_GET_VARS['totalRows_userList'];
} else {
$all_userList = mysql_query($query_userList);
$totalRows_userList = mysql_num_rows($all_userList);
}
$totalPages_userList = ceil($totalRows_userList/$maxRows_userList)-1;

$queryString_userList = "";
if (!empty($HTTP_SERVER_VARS['QUERY_STRING'])) {
$params = explode("&", $HTTP_SERVER_VARS['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_userList") == false &&
stristr($param, "totalRows_userList") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_userList = "&" . implode("&", $newParams);
}
}
$queryString_userList = sprintf("&totalRows_userList=%d%s", $totalRows_userList, $queryString_userList);

?>
<span class="smallstatsText">Elenco da <?php echo ($startRow_userList + 1) ?> a <?php echo min($startRow_userList + $maxRows_userList, $totalRows_userList) ?> di <?php echo $totalRows_userList ?> </span>

<table width="100%" border="0" cellspacing="0" cellpadding="2" class="supermenuNoshadow">
<tr>
<td width="8%" class="menuheader">user id</td>
<td width="12%" class="menuheader">username</td>
<td width="14%" class="menuheader">password</td>
<td width="24%" align="left" class="menuheader">email</td>
<td width="10%" align="center" class="menuheader">incremento</td>
<td width="10%" align="center" class="menuheader">punti</td>
<td width="12%" align="center" class="menuheader">attiva?</td>
<td width="10%" align="center" class="menuheaderAction">azione</td>
</tr>
<?php do { ?>
<tr>
<td class="smallstatsText"><?php echo $row_userList['id']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['username']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['password']; ?></td>
<td class="smallstatsText"><?php echo $row_userList['email']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList['point_inc']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList1['points']; ?></td>
<td align="center" class="smallstatsText"><?php echo $row_userList['verified']; ?></td>
<td align="center">[img]images/edit.gif[/img]
| [img]images/delete.gif[/img]</td>
</tr>
<?php } while ($row_userList = mysql_fetch_assoc($userList)and $row_userList1 = mysql_fetch_assoc($userList1));
?>


<tr>
<td height="15" colspan="6" align="center" >">[img]images/first.gif[/img]
">[img]images/prev.gif[/img]
">[img]images/next.gif[/img]
">[img]images/last.gif[/img]</td>
</tr>
</table>
<?php
mysql_free_result($userList);

?>