Jump to content
techker

updating table with for loop

Recommended Posts

Hey guys..i have an issue i can't seem to fix...

 

i have a table with codes and userid to associate  the code to a user

i need to make it that a user can give codes to another user.

i have a form .  from user to other user with amount of codes

 

so i have tried many combos..

$UserID =   mysqli_real_escape_string($DBcon, $_POST['OwnerID']);
$NewRId =   mysqli_real_escape_string($DBcon, $_POST['SubID']);
$Cr =       mysqli_real_escape_string($DBcon, $_POST['credits']);

tried with range

$t = array();
$counter = 0;
foreach (range(0, $Cr) as $t) {
	$sql = "UPDATE `Codes` SET `UserID`= '$NewRId' WHERE `UserID` = '$UserID'";
	$counter ++;
	echo $counter;
}
$result = mysqli_query($DBcon,$sql);
	if (!$result) {
	    trigger_error("UPDATE query failed: " . mysqli_error(), E_USER_ERROR);
	}
but it takes all the ocdes from user one to user two...

tried also:
 foreach ($Cr as $t) {
	$sql2 = "UPDATE `Codes` SET `UserID`= '$NewRId' WHERE `UserID` = '$UserID'";
 	if ($DBcon->query($sql2) === TRUE) {
     echo "<meta http-equiv=Refresh content=1;url=SwapC.php?success=1 >";
	} else {
		 echo "Error updating record: <br>" . $DBcon->error;
	}
 }
but does not work...

 

 

 

Share this post


Link to post
Share on other sites

In your first loop all you do is define a query string several times then execute it once.

What is $Cr?

If you never use it in a query why are you escaping it?

Much better to used prepared statements instead of escaping the data.

Not sure I really understand but it sounds like you want to do this...

$stmt = $DBCon->prepare("INSERT INTO codes (UserId, Code)
                            SELECT ?
                                 , Code
                            FROM codes
                            WHERE UserId = ?
                        ");
$stmt->bind_param('ss', $_POST['SubID'], $_POST['OwnerID']);
$stmt->execute();

 

Share this post


Link to post
Share on other sites

hey ya thx

$Cr = mysqli_real_escape_string($DBcon, $_POST['credits']);

is the number of credits the guy wants to give the other guy.

so lets say he has 20 codes and wants to give 10 to the other guy

 

in my codes table i have column codes and and column userID were i put the guys id (owner)

so now my issue is to take 10 codes out of 20 and swap his id to the new guys id

Share this post


Link to post
Share on other sites
Posted (edited)

also tried

$size = count($_POST['credits']);

$i = 0;
while ($i < $size) {
    $UserIDa= $_POST['OwnerID'][$i];
    $NewRIda = $_POST['SubID'][$i];

    $query = "UPDATE Codes SET UserID= '$UserIDa' WHERE UserID = '$NewRIda'";
    mysqli_query($DBcon,$query) or die ("Error in query: $query");
    echo "$UserIDa<br /><br /><em>Updated!</em><br /><br />";
    ++$i;
}

table code is set id value userid status dateSold

 

Edited by techker

Share this post


Link to post
Share on other sites

You seem intent on keeping the table secret so I'll guess.

/*       BEFORE 

+----------+--------+------+
| codes_id | UserId | code |
+----------+--------+------+
|        1 |      1 | aa   |
|        2 |      1 | bb   |
|        3 |      1 | cc   |
|        4 |      1 | dd   |
|        5 |      1 | ee   |
|        6 |      1 | ff   |
|        7 |      1 | gg   |
|        8 |      1 | hh   |
|        9 |      1 | jj   |
|       10 |      1 | kk   |
+----------+--------+------+   
*/

$userid = 1;
$subid  = 123;
$cr     = 5;

$stmt = $db->prepare("UPDATE codes
                            SET UserId = ?
                        WHERE userid = ?
                        LIMIT ?     
                  ");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();


/*    AFTER

+----------+--------+------+
| codes_id | UserId | code |
+----------+--------+------+
|        1 |    123 | aa   |
|        2 |    123 | bb   |
|        3 |    123 | cc   |
|        4 |    123 | dd   |
|        5 |    123 | ee   |
|        6 |      1 | ff   |
|        7 |      1 | gg   |
|        8 |      1 | hh   |
|        9 |      1 | jj   |
|       10 |      1 | kk   |
+----------+--------+------+
*/

 

Share this post


Link to post
Share on other sites
Posted (edited)

no lol

 

CID  Value DateIn Status UserID DateSold

i tried: but stays at same..

$userid = mysqli_real_escape_string($DBcon, $_POST['OwnerID']);
$subid  = mysqli_real_escape_string($DBcon, $_POST['SubID']);
$cr     = mysqli_real_escape_string($DBcon, $_POST['credits']);

$stmt = $DBcon->prepare("UPDATE Codes
                            SET UserID = ?
                        WHERE UserID = ?
                        LIMIT ?
                  ");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();

 

Edited by techker

Share this post


Link to post
Share on other sites

Well, I did ask and didn't tell me so I won't waste any more of my time LOL.

Share this post


Link to post
Share on other sites

what do you need??

CREATE TABLE `Codes` (
  `CID` int(11) NOT NULL,
  `Value` varchar(100) NOT NULL,
  `DateIn` varchar(25) NOT NULL,
  `Status` varchar(25) NOT NULL,
  `UserID` varchar(25) NOT NULL,
  `DateSold` varchar(25) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Share this post


Link to post
Share on other sites

What does a sample of your data look like before the query and what should it look like after?

I showed you mine, you show me yours.

  • Like 1

Share this post


Link to post
Share on other sites

not sure how to export it..lol

 

so basically:

CID    Value         DateIn            Status .       UserID .        DateSold

1 .     24242        2019-11-11 .    0                     5                 null

2.       24243        2019-11-11 .    0                     5                 null

3 .     24244        2019-11-11 .    0                     5                 null

4 .     24245        2019-11-11 .    0                     3                 null

5 .     24246       2019-11-11 .    0                     3                null

6.     24246       2019-11-11 .    0                     4               null

7 .     24246       2019-11-11 .    0                     4                null

user          is 5 .   want to give 2 codes(value) to user 3

any value(code) 

should be :

CID    Value         DateIn            Status .       UserID .        DateSold

1 .     24242        2019-11-11 .    0                     5                 null

2.       24243        2019-11-11 .    0                     3                 null

3 .     24244        2019-11-11 .    0                    3                 null

4 .     24245        2019-11-11 .    0                     3                 null

5 .     24246       2019-11-11 .    0                     3                null

6.     24246       2019-11-11 .    0                     4               null

7 .     24246       2019-11-11 .    0                     4                null

Share this post


Link to post
Share on other sites

That is precisely what the last example query that I posted is doing (except in mine it was user 1 giving 5 codes to user 123)

Share this post


Link to post
Share on other sites

ya its odd..why is it not working for me....

 

ini_set("display_errors", 1);
header("Refresh: 30");
require_once 'config/dbconnect.php';

$userid = $_POST['OwnerID'];
$subid  =  $_POST['SubID'];
$cr     = $_POST['credits'];

$stmt = $DBcon->prepare("UPDATE Codes SET UserID = ? WHERE UserID = ? LIMIT ?");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();

echo "$userid<br /><br /><em>$cr Updated! $subid</em><br /><br />";
echo "<meta http-equiv=Refresh content=1;url=SwapC.php?success=1 >";

?>

 

Share this post


Link to post
Share on other sites
Posted (edited)

Still working for me with your table format...

/*   BEFORE

+-----+-------+------------+--------+--------+----------+
| CID | Value | DateIn     | Status | UserID | DateSold |
+-----+-------+------------+--------+--------+----------+
|   1 | 24242 | 2019-11-11 |      0 |      5 | NULL     |
|   2 | 24243 | 2019-11-11 |      0 |      5 | NULL     |
|   3 | 24244 | 2019-11-11 |      0 |      5 | NULL     |
|   4 | 24245 | 2019-11-11 |      0 |      3 | NULL     |
|   5 | 24246 | 2019-11-11 |      0 |      3 | NULL     |
|   6 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
|   7 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
+-----+-------+------------+--------+--------+----------+
*/
            
$userid = 5;
$subid  = 3;
$cr     = 2;

$stmt = $db->prepare("UPDATE codes
                            SET UserId = ?
                        WHERE UserId = ?
                        LIMIT ?     
                  ");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();


/*    AFTER

+-----+-------+------------+--------+--------+----------+
| CID | Value | DateIn     | Status | UserID | DateSold |
+-----+-------+------------+--------+--------+----------+
|   1 | 24242 | 2019-11-11 |      0 |      3 | NULL     |    <--  changed user 5 to user 3
|   2 | 24243 | 2019-11-11 |      0 |      3 | NULL     |    <--  changed user 5 to user 3
|   3 | 24244 | 2019-11-11 |      0 |      5 | NULL     |
|   4 | 24245 | 2019-11-11 |      0 |      3 | NULL     |
|   5 | 24246 | 2019-11-11 |      0 |      3 | NULL     |
|   6 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
|   7 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
+-----+-------+------------+--------+--------+----------+
*/

Are you sure your $_POST contains what you think it should contain?

Edited by Barand

Share this post


Link to post
Share on other sites

im echoing  the correct POST

 

credits: 2 Updated! new owner: 36 from owner: 18

Share this post


Link to post
Share on other sites

ok odd it seems to be working now!lol.must of been my session was expirerd?let me test to see Thx!!

Share this post


Link to post
Share on other sites

thats odd i logout login works but after  that does not work anymore..even if i logout again...unless its a cash issue..

Share this post


Link to post
Share on other sites
Posted (edited)

Credits stay the same even do the echo says 2 credits from to ...

Edited by techker

Share this post


Link to post
Share on other sites

same issue very odd...it does not update at all...

require_once 'config/dbconnect.php';

  $userid = strip_tags($_GET['user']);
	$subid = strip_tags($_GET['newUser']);
  $cr = strip_tags($_GET['cr']);
	$userida = $DBcon->real_escape_string($userid);
  $subida = $DBcon->real_escape_string($subid);
  $cra = $DBcon->real_escape_string($cr);


$stmt = $DBcon->prepare("UPDATE Codes SET UserID = ? WHERE UserID = ? LIMIT ?");
$stmt->bind_param('iii', $subida, $userida, $cra);
$stmt->execute();

if(!$stmt->execute()) {
    echo "Error: " . mysqli_error($DBcon);
}else{
    echo "Success adding user : $subida with : $cra  ";
		echo "<meta http-equiv=Refresh content=1;url=Subseller.php?success=1 >";
}

 

Share this post


Link to post
Share on other sites
Posted (edited)

Uhh stupid me lol

So first issue was that i forgot to add  AND Status != 'Used' .  so it would update a used code..that is why i would not see it ..

 

But the issue i have now is that if i select 1 credit it puts 2

is it because it is like an array that 0 is 1 so it would do 0 then 1 so gives 2?

 

have a feeling it is this:

its were i add the user.it gets the amount of credits the user has(code) and puts them in a select for his to select the amount to transfer so he knows how many he can transfer..

$range = range(1,$CreditCount);
foreach ($range as $cm) {
  echo "<option value='$cm'>$cm</option>";
}
echo "</select>"; ?>

 

Edited by techker

Share this post


Link to post
Share on other sites

even with a different approach same thing if i put 1 it updates 2

for ($h = 1; $h <= $CreditCount; $h++) echo "<option value='$h'>$h</option>";
echo "</select>";

Share this post


Link to post
Share on other sites

This is my test version (with a couple of extra displays to confirm the processing)

<?php

## connect to your DB here ##
                                
$userid = '0';
$subid  = '0';
$cr     = 0;
$max    = 0;
$confirm = '';
$before = $after = '';
//
// was data posted?
//
if ($_SERVER['REQUEST_METHOD']=='POST') {

    $userid = $_POST['userid'] ?? 0;
    $subid  = $_POST['subid'] ?? 0;
    $cr     = $_POST['credits'] ?? 0;
    
    $before = "<h3>Before</h3>" . query2HTML($db, "select * from codes where userid in ($userid, $subid)");


    $stmt = $db->prepare("UPDATE codes
                            SET UserId = ?
                            WHERE UserId = ?
                              AND status != 'Used'    
                            LIMIT ?     
                      ");
    $stmt->bind_param('iii', $subid, $userid, $cr);
    $stmt->execute();
    $howmany = $db->affected_rows;
    
    if ($cr==$howmany)  {
        $confirm = "<div style='display:inline-block; padding: 16px; background-color: #EEE; color: #000'>$howmany rows transferred</div><br>";
    } else {
        $confirm = "<div style='display:inline-block; padding: 16px; background-color: #F00; color: #FFF'>$howmany rows transferred</div><br>";
    }

    $after  = "<h3>After</h3>" . query2HTML($db, "select * from codes where userid in ($userid, $subid)");
}
//
// respond to ajax request
//
if (isset($_GET['ajax'])) {
    exit(cr_options($db, $_GET['userid'], 0));
}

/**
* create list of credit quantity options
* 
* @param mysqli $db
* @param int $user
* @param int $current     - current quantity
*/
        function cr_options(mysqli $db, $user, $current)
        {
            $stmt = $db->prepare("SELECT COUNT(*) as tot
                                  FROM codes
                                  WHERE status != 'Used'
                                  AND UserId = ?
                                 ");
            $stmt->bind_param('i', $user);
            $stmt->execute();
            $stmt->bind_result($tot);
            $stmt->fetch();
            if ($tot == 0) {
                $opts = "<option value=''>No unused codes</option>";
            }
            else {
                for($i=1; $i<=$tot; $i++) {
                    $sel = $i==$current ? 'selected' : '';
                    $opts .= "<option $sel>$i</option>";
                }
            }
            return $opts;
        }
/**
* general purpose debugging utility function to output query results
* 
* @param mysqli connection $db
* @param string $sql
* @return string         - html table
*/
        function query2HTML($db, $sql)
        {
            $output = "<table border='1' cellpadding='2' style='border-collapse:collapse'>\n";
              // Query the database
            $result = $db->query($sql);
              // check for errors
            if (!$result) return ("$db->error <pre>$sql</pre>");
            if ($result->num_rows == 0) return "No matching records";
              // get the first row and display the headings
            $row = $result->fetch_assoc();
            $output .= "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
            
              // display the data
            do {
               $output .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n"; 
            } while ($row = $result->fetch_assoc());
            $output .= "</table>\n";
            return $output;
        }
?>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Sample Code Transfer</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript">
    $().ready(function() {
        $("#userid").change( function() {
            var user = $(this).val()
            $.get(
                "",
                {"ajax":1, "userid":user },
                function(resp) {
                    $("#credits").html(resp)
                },
                "TEXT"
            )
        })
    })
</script>
<style type="text/css">
    label {display:inline-block; width: 160px; font-weight: 600;}
</style>
</head>
<body>
<form method='post'>
    <fieldset>
    <legend>Codes Transfer</legend>
        <label>From user</label><input type="text" name="userid" id="userid" value="<?=$userid?>" size="5"><br>
        <label>Credits to transfer</label><select name="credits" id="credits"><?=cr_options($db, $userid, $cr)?></select><br><br>
        <label>To user</label><input type="text" name="subid" id="subid" value="<?=$subid?>" size="5"><br>
        <label>&nbsp;</label><input type="submit" name="btnSub" value="Transfer">
    </fieldset>
</form>
<br>
<br>
<?=$before?>
<?=$confirm?>
<?=$after?>
</body>
</html>

 

Share this post


Link to post
Share on other sites

would you know why it dow's one more than en what i ask?is it the select i did?that would calculate 0 as 1?

 

great example il try it out.again thx for the help!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.