Need some suggestions on a query to update a table

[rcane]

I have a profile field (custom) for members that only I can access.

I have a value=>key array that includes their id_member and the text value that goes with it.

I'm trying to get php to loop the array and perform an UPDATE to that field in the themes table, but it's not working--nor giving errors.

I can loop and echo the array to the screen so I know the values are working.

Such as this:

Code Select Expand

foreach ($specificmembers as $spec) {
   
    foreach ($spec as $key => $value) {
     
      $id = $key;
      $text = $value;
     
      echo 'user:  ' . $id . ' has the textvalue of: ' . $text . '<br>';
      }         
}
Which outputs something like this:

Code Select Expand

user: 845 has the textvalue of: sampletext
user: 900 has the textvalue of: apple
user: 2934 has the textvalue of: honda

But when I try to tuck an UPDATE SET query in there,  the php pages load, the error log gets nothing, and no members get the new data in their field know as 'cust_abc'.

Code Select Expand


foreach ($specificmembers as $spec) {
   
    foreach ($spec as $key => $value) {
     
      $id = $key;
      $text = $value;
     
        $conn->query ("
        UPDATE smfqg_themes
        SET `value` = $text
        WHERE `id_member` = $id
        AND
        `variable` = 'cust_abc'
     
      ");
    }     
}


The query works if I just hand-jam a known value in for $text and $id, but it doesn't like the array--the way that just looping it to echo did.

[vbgamer45]

put single quotes around '$text'

Also if you data has single quotes you need to escape it first such as addslashes($text)

[rcane]

I'll give that a try.

Also, I just realized this should be in the coding board and not in 2.0 support.  Sorry.

[Doug Heffernan]

Also, I just realized this should be in the coding board and not in 2.0 support.  Sorry.

No problem. I have moved this for you.

May I ask why you are asigning variables on variables? For example:

$id = $key;
$text = $value;

You can use $key and $value directly. It is easier to keep track of the code this way.

[rcane]

Also, I just realized this should be in the coding board and not in 2.0 support.  Sorry.

No problem. I have moved this for you.

May I ask why you are asigning variables on variables? For example:

$id = $key;
$text = $value;

You can use $key and $value directly. It is easier to keep track of the code this way.

I changed the code to what's there before I realized it was a different part of the code block that was making the error and not my variables.  I should change them back.


In any case, the problem appears to be this: 

The custom profile field I made isn't seen by anyone, therefore no one has made an entry to it, therefore it has no value.  If you run a query on any user, that 'cust_abc' in the variable column doesn't exist in the `value` column.  Because they (meaning me) have't entered it yet.

So, an UPDATE doesn't work.  The code solution above using '' around $text works but only on the few accounts I've fooled with (8 of them) where a value was actually made at some time.

So, I need an INSERT, but it's got to come with a 'where' which can be done in mysql but not clearly understood for the way I have to go about it.

I need to get a default value of some text or '' in `cust_abc' => `value` to get started.

INSERT into smfqg_themes (`value`)
VALUES ('placeholdertext`)
WHERE `variable` = 'cust_abc'


I know that's not a real syntax, but it always gets a little fuzzy working with the themes table because of how it needed to be set up for SMF.

Then, I can go in there and run it again as an UPDATE changing everything from 'placeholdertext' to whatever I need, by specific memberGroup/id_member/or whathaveyou.


I know you can INSERT with a WHERE using SELECT, but it's not entirely clear since I am working in the same table.

[vbgamer45]

You can do an insert with a select as long as column count matches

INSERT INTO tablename (COL1,COL2) select col1, col2 from tablename where condition = true

[rcane]

You can do an insert with a select as long as column count matches

INSERT INTO tablename (COL1,COL2) select col1, col2 from tablename where condition = true

But this looks like you can only insert existing info, no?

The problem seems to be that, even though the custom_profile_fields table HAS the custom field, there do not exist any records in any member because I haven't entered them.

In other words, the themes table has no 'cust_abc' for anyone in the `variable` col.


So, I can't UPDATE that which isn't there.


I need to make a 'cust_abc' in the `variable` col for each member and THEN I'd have to do an UPDATE on those to actually put something in there?

[vbgamer45]

You can insert values such as select 'VALUEHERE', col2 FROM

[rcane]

You can insert values such as select 'VALUEHERE', col2 FROM

At the risk of sounding obtuse, can you give me a syntactical example using what I have?

Because I'm not selecting any existing value.  I'm "writing" a textString (let's just say the word 'stuff') into the the themes table column known as themestable.value when the corresponding themestable.variable column has the custom field titled 'cust_abc'.

All the examples of INSERT that use SELECT (and WHERE) seem to be geared to pulling data that exists already in some other table or column.   Mine doesn't exist that way. 

Also, I don't know what the id_theme is in the themes table, so I wouldn't know how to apply any numerical value to it.

[vbgamer45]

something like this?

Code Select Expand

INSERT into smf_themes (id_member,id_theme,value) SELECT id_member,id_theme,'stuff' where variable = 'cust_abc'