Help with syntax

[Miss-Ruth]

I'm trying to update each row in my table with $upoin. But doesn't. No errors shown but it dosen't update the database (table) with $upoin.

 

$count1 = array_sum($count);
$upoin = $count1;

$query=mysql_query("SELECT * FROM table");
$rownum=mysql_num_rows($query);
for($i=0;$i<$rownum;$i++)
{
    $query=mysql_query("SELECT * FROM table LIMIT $i,1");
    while($select=mysql_fetch_array($query))
    {
         $update=mysql_query("UPDATE table SET cat_num=$upoin");
    }
}

 

Help fix the code.

Thanks,

Ruth \-)

[Killer1390]

I believe the problem is this line:

$update=mysql_query("UPDATE table SET cat_num=$upoin");

 

Php has a weird quote system and whatnot. This should fix it:

$update=mysql_query("UPDATE table SET cat_num='$upoin'");

 

I think it should anyways, I am new to this. Just look at the end of the line of code to see what I did.

[kenrbnsn]

If you want to update every row with that value, you don't have to that in a loop. Just do one update statement:

<?php
$q = "UPDATE table SET cat_num=$upoin";
$rs = mysql_query($q) or die("Problem with the update: $q<br>" . mysql_error());
?>

 

Ken

[Miss-Ruth]

Killer1390 - Thanks for your input.

 

kenrbnsn - Thanks. But below is my full script and I was trying to update each row with a different value.

In that script I'm trying to:

get data from two databases (sub_db and master_db)

and match the data between the two databases

then get the count of the matching data.

(I managed to succeed up to this stage)

now i want to match data on each row of a database against only the first row of the other database. (E.g. all rows of sub_db against master_db's first row)

then update the column "the_points" for each row.

 

<?php
$conn = mysql_connect("localhost","un","pw");
mysql_select_db("the_db");

$ArrayUs1 = array();
$ArrayMk1 = array();
//master_db
$query1 =  "SELECT * FROM master_db WHERE round_one = round_one";
$result = mysql_query($query1);
$scoreM = mysql_fetch_assoc($result);
foreach (range('a','h') as $ltr) {
$ArrayMk1[] = array($scoreM['my_win' . $ltr]);
}

//sub_db
$query=mysql_query("SELECT * FROM sub_db");
$rownum=mysql_num_rows($query);
for($i=0;$i<$rownum;$i++)
{
    $query=mysql_query("SELECT * FROM sub_db LIMIT $i,1");
    while($select=mysql_fetch_array($query))
    {
$scoreU = mysql_fetch_assoc($select);
foreach (range('a','h') as $ltr) {
$ArrayUs1[] = array($scoreU['first_round' . $ltr]);
}
$count = array();
for($i=0;$i<count($ArrayUs1);++$i) {
$count[$i] = count(array_intersect($ArrayUs1[$i],$ArrayMk1[$i]));
}
$count1 = array_sum($count);
$my_poins = $count1;
$update=mysql_query("UPDATE sub_db SET the_points='$my_poins'");
    }
}
?>

 

I really appreciate your help.

Thanks,

Ruth \-)

[Miss-Ruth]

ok... I got it fixed to a certain extent. But my loop is not functioning. Could someone help in fixing this so it loops through all the rows.

 

//loop through all rows in my_db

$query=mysql_query("SELECT * FROM my_db");

$rownum=mysql_num_rows($query);

for($i=0;$i<$rownum;$i++)

{

    $query=mysql_query("SELECT * FROM my_db LIMIT $i,1");

    while($scoreU=mysql_fetch_array($query))

    {

foreach (range('a','h') as $ltr) {

$ArrayNew[] = array($scoreU['roundzA' . $ltr]);

}

$count = array();

for($i=0;$i<count($ArrayNew);++$i) {

$count[$i] = count(array_intersect($ArrayNew[$i],$ArrayOld[$i]));

}

$count1 = array_sum($count);

$My_points = $count1;

$update=mysql_query("UPDATE my_db SET points='$My_points'");

    }

}

[Miss-Ruth]

Could anyone help me in getting the array loop fixed please?

 

Ruth.

[kenrbnsn]

This code doesn't make sense. What are you trying to do? If you can show us the structure and contents of your database, it might help some.

 

Ken

[Miss-Ruth]

Hi Ken, here is a module of my project.

 

my_db1 example structure

student  |  exama  |  examb  |  examc  | examd .... up to examh  |  Achieved

Ruth        |      A+      |      C      |      C      |    A+  .... up to      C    |     

Martin    |      A+    |      C      |      B      | examd .... up to      A    |

Nathan  |      A+    |      A      |      C      | examd .... up to      C    |

//40 students are in the db.

 

my_db2 example structure

semester  |  qualifieda  |  qualifiedb  |  qualifiedc  | qualifiedd .... up to qualifiedh

semester1 |        A+      |        A        |        B        |        B        .... up to        A

//end of db

 

I want to update each row in Achieved with the COUNT of the matching results in exama, examb, examc etc...  of my_db1 against qualifieda, qualifiedb, qualifiedc, etc... in my_db2.

 

In this example the Achieved column should fill as

Achieved

        1

        3

        2

 

Hope it makes sense.

 

Thanks,

Ruth.

[trq]

Your database design is worse than your code. Sheesh.

[Miss-Ruth]

Those are existing databases.

 

How do I update each row in Achieved?

[Anti-Moronic]

What you need to do is redesign the database. The rest will take care of itself (to a degree).

[Miss-Ruth]

Isn't there a solution as it is?

 

I managed to the calculations and the count. The only thing I'm missing here is that how to update each row with the count value. the array simply won't do it.

 

Ruth.