Using a function within a loop to update mysql database field

Page 1 of 1

9 Replies - 370 Views - Last Post: 01 October 2012 - 06:33 AM Rate Topic: -----

#1 tysons engineer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-September 12

Using a function within a loop to update mysql database field

Posted 28 September 2012 - 01:32 PM

I have read a lot of posts on this website dancing around this subject but for some reason I still can't get this functionality to work by combining what I have read. The end goal is as such;

I want to be able to have the table take the field post_id = 2, 5, 8, 14 and then update the field named order for each of those rows to say 1, 2, 3, 4 within that loop. The end game is to be able to resort on a per page basis a group of database #id back into 1,2,3,4... depending on which category I load. All of this so I can have a two column category filtered database (odd on left column even on the right). I have been racking my brain for nearly a week and can't believe there is no bi-dimensional repeat region capability in DW6 that could solve this. Thank you in advance

mysql_select_db($database_connTest, $connTest);
$query_RS_Odd = "SELECT projects.post_id, projects.feature_image, projects.`order` FROM projects WHERE projects.landscape = 'landscape'";
$RS_Odd = mysql_query($query_RS_Odd, $connTest) or die(mysql_error());
$row_RS_Odd = mysql_fetch_assoc($RS_Odd);
$totalRows_RS_Odd = mysql_num_rows($RS_Odd);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="styles/Cowen_Website.css" rel="stylesheet" type="text/css" />
</head>

<body>

<table>
	<?php do { ?>
	<img src="<?php echo $row_RS_Odd['feature_image']; ?>" style="width:30%; margin-bottom:10%; margin-left:auto; margin-right:auto; display:block;"/>
    <p style="text-align:center"><?php
	if ($row_RS_Odd['post_id'] == 1){ 
		$row_RS_Odd['order']=1;
		$resort_next = 1;
		echo $row_RS_Odd['post_id'];
	mysql_query("UPDATE project SET order=$resort_next WHERE post_id=%s");
	}
	else if ($row_RS_Odd['post_id'] != 1 && $next==NULL)
	{
	$resort_next = 1;
	$next = $resort_next + 1;
	$row_RS_Odd['order']=$resort_next;
	echo $resort_next;
	echo $row_RS_Odd['post_id'];
	mysql_query("UPDATE project SET order=$resort_next WHERE post_id=%s");
	}
	else if ($row_RS_Odd['post_id'] != 1 && $next!=NULL)
	{
	$resort_next = $next;
	$next = $resort_next + 1;	
	$row_RS_Odd['order']=$resort_next;
	echo $resort_next;
	echo $row_RS_Odd['post_id'];
	mysql_query("UPDATE project SET order=$resort_next WHERE post_id=%s");
	}
	else {
	echo 'No records found';
	}
	mysql_query("UPDATE projects SET order=3 WHERE post_id=3");
	?>
    
	<?php } while ($row_RS_Odd = mysql_fetch_assoc($RS_Odd)); ?></p>
    <?php
	$next=0;
	?>
</table>

</body>
</html>


<?php
mysql_free_result($RS_Odd);
?>


I should add that I am able to get it to report the variable $resort_next with each row perfectly so that it does indeed echo the correct accompanying resort_next# with the post_id# as you can see in the code. The problem is then after it echos that, I cant seem to get my database to update with that variable (no syntax errors or anything, just wont update)

Is This A Good Question/Topic? 0
  • +

Replies To: Using a function within a loop to update mysql database field

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5672
  • View blog
  • Posts: 22,527
  • Joined: 23-August 08

Re: Using a function within a loop to update mysql database field

Posted 28 September 2012 - 02:18 PM 

mysql_query("UPDATE project SET order=$resort_next WHERE post_id=%s");


What exactly do you expect to magically appear in %s there?

Honestly, on the whole this looks like pretty horrible code. Lots of repetition, for example. Using the deprecated mysql_ functions, is another.

Your dependence on DW, rather than learning web development from the bottom up, is no doubt hobbling you.
Was This Post Helpful? 0
  • +
  • -

#3 tysons engineer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-September 12

Re: Using a function within a loop to update mysql database field

Posted 28 September 2012 - 02:28 PM

I have no dependence on either because I am new in both ways. I typically use dreamweaver only to fill out syntax not for WYSIWYG function. What I lack is syntax knowledge either way. I have only been quilting together what I can gather for about 2 weeks, before that I knew absolutely nothing about php. But your critique is accepted, and I will continue to try to get better for the remainder of my life beyond the past 2 weeks.

%s was actually accidently put in there, the goal was to use the $row_RS_Odd['post_id']. Either way I know that isnt the issue because even when I tell it specifically to use post_id=3 which in this case I know is an ID number that should pass through, it still does not store the variable in the field.

Any thoughts?
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 745
  • View blog
  • Posts: 1,525
  • Joined: 30-January 09

Re: Using a function within a loop to update mysql database field

Posted 28 September 2012 - 02:37 PM

I'm not even sure why you're using this complex looping structure in PHP. You could achieve this inline by using a ranking variable:
SELECT
  projects.post_id, 
  projects.feature_image,
  projects.`order`,
  @rank := @rank + 1 AS rank
FROM projects
CROSS JOIN (
  SELECT @rank = 0
) AS r
WHERE projects.landscape = 'landscape'
ORDER BY projects.post_id

Also, why are you updating the order column in the table? Is this a run-once function to set a table field, or will this be run multiple times over the lifetime of the application?

This post has been edited by e_i_pi: 28 September 2012 - 02:39 PM
Reason for edit:: Forgot to put in the ORDER BY clause

Was This Post Helpful? 1
  • +
  • -

#5 tysons engineer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-September 12

Re: Using a function within a loop to update mysql database field

Posted 28 September 2012 - 02:54 PM

order is basically a temporary field. I am not sure what the rank variable does but I will read into it.

So this might be an issue of me not realizing there is something does it simpler (do to my lack of knowledge of all of the different options out there). The reason I went with this method was because I couldnt figure out how to get the posts to form a 2 wide column 3 rows tall with the 'featured_image' field based on their post_id without first making it so the left column was only odd, and the right column was even (you dont see that in the code because that is a separate file that I got to work). The problem became when I wanted to take that database and slice it to only see landscape category. Now the post_id couldnt be used because if the post_id # were 2,4,5,7,10,11 it would show as

5 2
7 4
11 10

which isn't my desired output. So going down the rabbit hole of how I started, I said well if I can create another variable that re-sorts those post_id # to turn it into 1,2,3,4,5,6 dependent on the category called upon just temporarily then I could have my recordset in the correct order. So I got the reordering to work fine, but the last step was I couldnt get it to update the field and thereby beable to recall the new sorted order.

I hope that makes sense, I will read up on @rank because to be honest I've never heard of it before

Sorry I'm an idiot, you mean why not use a variable in the query itself that would act like a rank.

I couldn't figure how to get it to order by the variable instead of sorting by the post_id which is still outputting in the wrong order in my 2 column display given the above situation
Was This Post Helpful? 0
  • +
  • -

#6 Nullified  Icon User is offline

  • New D.I.C Head

Reputation: 13
  • View blog
  • Posts: 47
  • Joined: 18-September 12

Re: Using a function within a loop to update mysql database field

Posted 28 September 2012 - 07:05 PM

As others have said, read up on using mysqli or pdo instead of soon to be depreciated mysql_. Many times advancing your sql queries is a more efficient way of handling data than relying on php to conduct reorganization cycles. Also, ensure that when working with databases and user input data you escape and validate your post variables before using them in sql queries (you may be doing so in your forms, just wanted to make sure). Make sure you escape your strings and use intval for your integers.

This post has been edited by Nullified: 28 September 2012 - 07:08 PM

Was This Post Helpful? 1
  • +
  • -

#7 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 745
  • View blog
  • Posts: 1,525
  • Joined: 30-January 09

Re: Using a function within a loop to update mysql database field

Posted 28 September 2012 - 07:30 PM

The @ symbol in SQL simply means "variable". There's no point looking up @rank, it would be like searching for $myVariable.

If you're looking to order by the variable, you should be able to use the clause ORDER BY @rank. If that doesn't work, then you can wrap your statement in another SELECT and then use the ORDER BY, like this:
SELECT *
FROM (
  // Enter the other query here
) AS SubQuery
ORDER BY rank

Was This Post Helpful? 1
  • +
  • -

#8 tysons engineer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-September 12

Re: Using a function within a loop to update mysql database field

Posted 29 September 2012 - 05:19 AM

View Poste_i_pi, on 28 September 2012 - 07:30 PM, said:

The @ symbol in SQL simply means "variable". There's no point looking up @rank, it would be like searching for $myVariable.

If you're looking to order by the variable, you should be able to use the clause ORDER BY @rank. If that doesn't work, then you can wrap your statement in another SELECT and then use the ORDER BY, like this:
SELECT *
FROM (
  // Enter the other query here
) AS SubQuery
ORDER BY rank


Ah see and I didnt even realize that it could be done. Chalk this up to pure newbie oversight. Question, as people have pointed out, I am clearly very raw on what can be done in php vs sql. What would be the best books for me to order to continue to learn. I unfortunately right now have only DW6 Bible which helps to some extent but doesn't get into real programming, it really is more geared toward static site development.

SO any recommendations on websites that have a good index of concepts and any books would be appreciated. And I really appreciate the assistance on using variables in the SQL code to re-sort my posts.
Was This Post Helpful? 0
  • +
  • -

#9 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 745
  • View blog
  • Posts: 1,525
  • Joined: 30-January 09

Re: Using a function within a loop to update mysql database field

Posted 29 September 2012 - 03:43 PM

There two quite good interactive online tutorials that I know of:

Was This Post Helpful? 0
  • +
  • -

#10 tysons engineer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-September 12

Re: Using a function within a loop to update mysql database field

Posted 01 October 2012 - 06:33 AM

View Poste_i_pi, on 28 September 2012 - 07:30 PM, said:

The @ symbol in SQL simply means "variable". There's no point looking up @rank, it would be like searching for $myVariable.

If you're looking to order by the variable, you should be able to use the clause ORDER BY @rank. If that doesn't work, then you can wrap your statement in another SELECT and then use the ORDER BY, like this:
SELECT *
FROM (
  // Enter the other query here
) AS SubQuery
ORDER BY rank


I just realized, this wont help me. The ranking will not actually renumber back to 1, 2, 3, 4 which is the problem I am having with being two columns.

Right now I have two recordsets one for even, one for odd. If I add that rank function to each of those recordsets it will still output

1 4
3 6
9 8

Which is not going to work. That is why I wanted to set up a second variable that actually goes to a field in the database that arches over both record sets.

So I guess is there anyway someone can help explain why my output to the SQL table isnt taking?

As a broad overview of the function I am trying to get to;

Page loads
1 recordset takes all of categorized post_id# (1, 2, 4, 5, 8, 12...etc) and renumbers specifically 1, 2, 3, 4, 5... etc
That renumbering for each post_id# would then send an update to the SQL in a field that is provided called order which would take those values.
Then 2 recordsets (one odd, one even) load not the post_id but the order# and all the relevant information (in this case a featured image) and output on the screen two columns of pictures that are in correct order based on whatever category is loaded.

Thanks

PS I have everything working that can do that, except for the one part that is most important, sending the reordered numbers that I have created to a field in SQL while within a Loop
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1