Select and update querry - Page 2

[ryantroop]

ok, first - no matter how many <? ?> you have on your page, you only need to connect once. Ideally in the original <?php .... ?> pair, but it persists on the page as a whole, regardless of embedded HTML.

line 68, 69, 71 - You are doing the same operation twice, and in fact you are ignoring data.
$num = mysql_num_rows($query); // this is meant to check the number of rows returned. That's it!

so if by calling that, and then checking if(!$result) {} you essentially just asked if anything was there, assign that number to $num. If something is there, then do something. And then you forget about $num.

it is usually used as:

$result = mysql_query($query);
$num = mysql_num_rows($result);
if ($num > 0) { //run my script } else { //oops! No data! }

Now, as for the rest of what you have going on.. I think you need to be a little more clear.

Are you having trouble with your data displaying properly? Or is it not updating properly with the 2nd page?

[Stardemos]

i've trouble in bouth, file 1 don't show any records, and 2nd file don't update them...

[ryantroop]

Ok.. so this is a basic structure that should get you going in the right direction (I hope).

<?php
$usr = 'sqlusr';
$pw = 'sqlpw';
$host = 'sqlhost';

$db = mysql_connect($host, $usr, $pw);
mysql_select_db('dbname');

$nordem = $_POST['nordem'];
$nordem = mysql_real_escape_string($nordem);

$query = "SELECT * FROM entrada WHERE nodem_n = '$nordem';";
$result = mysql_query($query);

while ($data = mysql_fetch_object($data)) {
?>
<table><form action='mypage.php' method='post'>
<tr>
 <td>Field 1</td><td>Field 2</td><td>Field 3</td>
</tr>
<tr>
 <td><? $data->col1; ?></td><td><? $data->col2; ?></td><td><? $data->col3; ?></td>
</tr>
<tr>
 <td><input type='submit' value='submit'></td><td><input type='hidden' name='id' value='<? $data->id; ?>'></td>
</tr>
</form>
</table>
<?
}
?>

Now, lets assume that there is only 1 entry, we should only get 1 form back. This is completely bare bones, and has no functionality other than sending a blank post to mypage.php.

For reference.. instead of making an array, I made an object called $data. Inside data, all the columns are stored by keyname. So, $data->id will give the value of the id column in your sql table.

Now, we can further configure our form, to make our default value of a text field.. so lets say we were doing 'Field 1' value...

on the next row, the first <td></td> we would put

<td><input type='textbox' name='whatever' value='<? data->whatever ?>'></td>

Then, when we hit submit, we now have a POST value of 'whatever' being sent to our mypage.php

Then, on mypage.php we can do something with it.

<? if(isset($_POST['whatever']){
//run some program/do sql query or update or whatever
}
?>

That's it... you will notice that even if we call back multiple results from our SQL query, since we used a while loop, we will print out multiple tables that will be identified by POST through id.. you will also note that this will limit you to only 1 update, as $_POST['id'] will be unique for each submit button. If you wanted to be able to change all the fields on all the results, you will have to either make the post a 2 dimensional array, or modify the name field for each element in the post using php. However, start with just 1 at a time, and work on making it work for that one form.

[Stardemos]

that gave 2 erros that i will find out how to solve.. one of those, is more complicated..
...Warning:
mysql_fetch_object(): supplied argument is not a valid MySQL result resource C:\wamp\www\8.8\new.php on line 9

the other:
Notice: Undefined variable: dados in C:\wamp\www\8.8\new.php on line 9

but afterall it seams to be a good solution, the only problem is that i might need to update multiple rows...

[pritaeas]

supplied argument is not a valid MySQL result resource

Your query failed.

[Stardemos]

failed? why? righ't now i'm totaly confused....

[pritaeas]

If it is the query at line 12 above, probably because of the typo... I suggest you use the following when debugging:

$result = mysql_query($query) or die(mysql_error());

[Stardemos]

$result = mysql_query($query) or die(mysql_error());

thanks, unfortunatly, after the modification, the same error presists, maybe this line of code isn't compatible with my mySql 5.5.24, or my php 5.4.3

[Stardemos]

i was able to solve that problem, finaly|! now cames one even worst, the tables created by this files are empty, and i'm sore that i made the rigth search, and i can see that because the file repeats the line 6 times, same number of rows that i have.

<?php
mysql_connect("localhost", "root")or die("sem conecção ao servidor"); 
mysql_select_db("sermail")or die("Base de dados não foi encontrada");

$nordem = $_POST['nordem'];
$nordem = mysql_real_escape_string($nordem);
$query = "SELECT * FROM entrada WHERE nordem_n = '$nordem'";
    $result = mysql_query($query) or die(mysql_error());
while ($dados = mysql_fetch_object($result)) {
?>
<table><form action='mypage.php' method='post'>
<tr>
<td>Código Produto</td><td>Código Cliente</br>1-Lote</td><td>Código Cliente</br>2-Validade</td><td>Designação</td><td>Filtro 1</br> unidade de</br>Negocio</td><td>Familia/Marca</td><td>Quantidade Guia</td><Td>Quantidade conferida</td><td>Atado</td><td>Posição</td>
</tr>
<tr>
<td><?php $dados->codprod; ?></td><td><?php $dados->lote; ?></td><td><?php $dados->val; ?></td><td><?php $dados->designacao; ?></td><td><?php $dados->filtro; ?></td><td><?php $dados->familiamarca; ?></td><td><?php $dados->quantguia; ?></td><td><?php $dados->quantconf; ?></td><td><?php $dados->atado; ?></td>
<td><?php $dados->posicao; ?></td>
</tr>
<tr>
<td><input type='submit' value='submit'></td><td><input type='hidden' name='id' value='<?php $dados->id; ?>'></td>
</tr>
</form>
</table>
<?php
}
?>

[pritaeas]

You forgot echo ...

[Stardemos]

true....

[Stardemos]

it seems to be working just fine! but how do I do the update query?

[pritaeas]

Didn't we already discuss that in your other thread?

[Stardemos]

No, in the other thread we discuss the multiple row insert query.

[pritaeas]

Although the query is a little different, that doesn't mean the logic is.

[Stardemos]

Yes, your are right, the logic is the same, only the query is changed, but, the result os my search for the solution of this case, is that UPDATE query needs to be done with CASE, and even that way, i couldm't put it to work.

[Stardemos]

as you told me to, i'm using the same logic as in insert into multiple rows code.
like this:

<?php
    //conecção ao servidor
    $conn = mysql_connect("localhost", "root")or die("sem conecção ao servidor");
    $db = mysql_select_db("sermail")or die("Base de dados não foi encontrada");
    //update

    if ( isset ( $_POST['submit'])){
    $sql2a = "UPDATE entrada SET ";
    for ($i=0; $i<count($_POST['check']); $i++) {
    if ($_POST['check'][$i] == 0) continue;
     $sql2a .="
     codprod={$_POST['codprod'][$i]},
     lote= {$_POST['lote'][$i]},
     val={$_POST['val'][$i]},
     designacao={$_POST['designacao'][$i]},
     filtro={$_POST['filtro'][$i]},
     familiamarca={$_POST['familiamarca'][$i]},
     quantguia={$_POST['quantguia'][$i]},
     quantconf={$_POST['quantconf'][$i]},
     atado={$_POST['atado'][$i]},
     posicao={$_POST['posicao'][$i]}"  ;

   if ($i<count($_POST['check']) - 1) {
    $sql2a .= '';
    $sql2a .="WHERE {$_POST['id'][$i]} = $id";
    }
    }}
    mysql_query($sql2a) or die(mysql_error());
    echo $sql2a;echo"</br>";

    // pagina inicial, update.html
    Echo "<a href='pesquisar.html'>Nova pesquisa</a>";
    ?>
    <?php
    mysql_close();
    ?>

it gaves me syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' designacao=4, filtro=4, familiamarca=4, quantguia=7, ' at line 4.

I need help...please!

[pritaeas]

Looks like there is no space before the WHERE. Just a guess. If you post your query too it's much easier to spot.

[Stardemos]

by query do you mean the 1st file?

<?php
mysql_connect("localhost", "root")or die("sem conecção ao servidor"); 
mysql_select_db("sermail")or die("Base de dados não foi encontrada");

$nordem = $_POST['nordem'];
$nordem = mysql_real_escape_string($nordem);
$query = "SELECT * FROM entrada WHERE nordem_n = '$nordem'";
    $result = mysql_query($query) or die(mysql_error());
while ($dados = mysql_fetch_object($result)) {
?>
<table border="1" cellspacing="0"><form action='umprq2.php' method='post'>
<tr>
<td>Código Produto</td><td>Código Cliente</br>1-Lote</td><td>Código Cliente</br>2-Validade</td><td>Designação</td><td>Filtro 1</br> unidade de</br>Negocio</td><td>Familia/Marca</td><td>Quantidade Guia</td><Td>Quantidade conferida</td><td>Atado</td><td>Posição</td>
</tr>
<tr>
<td><input type='textbox' name='codprod[]' value="<?php echo $dados->codprod; ?>"</td><td><input type='textbox' name='lote[]' value="<?php echo $dados->lote; ?>"</td><td><input type='textbox' name='val[]' value="<?php $dados->val; ?>"</td><td><input type='textbox' name='designacao[]' value="<?php echo $dados->designacao; ?>"</td><td><input type='textbox' name='filtro[]' value="<?php echo $dados->filtro; ?>"</td><td><input type='textbox' name='familiamarca[]' value="<?php echo $dados->familiamarca; ?>"</td><td><input type='textbox' name='quantguia[]' value="<?php echo $dados->quantguia; ?>"</td><td><input type='textbox' name='quantconf[]' value="<?php echo $dados->quantconf; ?>"</td>
<td><input type='textbox' name='atado[]' value="<?php echo $dados->atado; ?>"</td>    <td><input type='textbox' name='posicao[]' value="<?php echo $dados->posicao; ?>"</td><td><input type='checkbox' name='check[]' value="1"</td>
</tr>
<tr>
</td><td><input type='hidden' name='id' value='<?php $dados->id; ?>'></td>

<?php

}
?>
<td><input type='submit' name="submit" value='Alterar'>
</tr>
</form>
</table>

[Stardemos]

well, it seems that i put it to work again, but the same problem pressists, it update all the rows in the database, not only where nordem=$nordem.

[pritaeas]

No. I meant the SQL query which triggered the error.

If it updates all rows, show your generated UPDATE query. The problem is most likely, that with the UPDATE query you should run all six separately (in contrary to the INSERT, in which six rows can be inserted at once).

[Stardemos]

output only this: UPDATE entrada SET UPDATE entrada SET codprod=1123, lote= 6, val=1, designacao=4, filtro=4, familiamarca=4, quantguia=7, quantconf=8, atado=7, posicao=821

the query

$sql2a = "UPDATE entrada SET ";
    for ($i=0; $i<count($_POST['check']); $i++) {
    if ($_POST['check'][$i] == 0) continue;
    $sql2a .="
    codprod={$_POST['codprod'][$i]},
    lote= {$_POST['lote'][$i]},
    val={$_POST['val'][$i]},
    designacao={$_POST['designacao'][$i]},
    filtro={$_POST['filtro'][$i]},
    familiamarca={$_POST['familiamarca'][$i]},
    quantguia={$_POST['quantguia'][$i]},
    quantconf={$_POST['quantconf'][$i]},
    atado={$_POST['atado'][$i]},
    posicao={$_POST['posicao'][$i]}" ;
    if ($i<count($_POST['check']) - 1) {
    $sql2a .= '';
    $sql2a .=" WHERE {$_POST['id'][$i]} = $id";
    }
    }

[pritaeas]

See, the WHERE is missing. You'll need something like this:

for ($i = 0; $i < count($_POST['check']); $i++) {
    if ($_POST['check'][$i] == 0) continue;

    $sql2a = "UPDATE entrada SET 
        codprod={$_POST['codprod'][$i]},
        lote= {$_POST['lote'][$i]},
        val={$_POST['val'][$i]},
        designacao={$_POST['designacao'][$i]},
        filtro={$_POST['filtro'][$i]},
        familiamarca={$_POST['familiamarca'][$i]},
        quantguia={$_POST['quantguia'][$i]},
        quantconf={$_POST['quantconf'][$i]},
        atado={$_POST['atado'][$i]},
        posicao={$_POST['posicao'][$i]}
        WHERE id = {$_POST['id'][$i]}";

    mysql_query($sql2a) or die(mysql_error() . '<br/>' . $sql2a);
}

[Stardemos]

ok, thanks, in my "nut"head, the where could be in a $sql2a .= ...
after tried the code, the output is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12
UPDATE entrada SET codprod=112, lote= 6, val=1, designacao=4, filtro=4, familiamarca=4, quantguia=7, quantconf=8, atado=7, posicao=821 WHERE id =

if i finde the error before you i post as usual.

thanks again

[Stardemos]

Working!

thank you both for your help, time, pacience and anything else!

the only problem right now must be in db, when I isert a value with a letter, he said that, that field dosen't exist. must be a problem with the vars.

[Stardemos]

after testing, not a problem in db. i will opem new thread