Solved

insert query not working on server only on localhost

Posted on 2010-09-20
8
335 Views
Last Modified: 2012-05-10
Hi,

I have a insert query that does not seem to work.

 

I am creating a entry into matches to get match_id and then use match_id to populate the rest of the info into the other table.

 

It works on localhost, but not on my server. It gives me this:
Warning:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/site/create-fixtures.php on line 39

line 39 is: while($match = mysql_fetch_assoc($result))
$season_id = date('Y');

 

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}

Open in new window

0
Comment
Question by:cataleptic_state
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 

Expert Comment

by:orsobruno
ID: 33714613
try to correct the query

SELECT h.team_id as hometeam, a.team_id as awayteam FROM team AS h, team AS a WHERE h.team_id <> a.team_id
0
 
LVL 2

Expert Comment

by:rameshfromind
ID: 33714852
I think this query will return the null value thats why getting error if there is any value available only control enter the while loop.

1. There is no data in server database thourgh the condition[h.team_id <> a.team_id].
2. But you have  the data in local server.

'SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id'

If you need any assistance let me know.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33716058
Hi,
the team information is in the server database.

Also on some of the other pages it says "No database selected"
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33724490
When you are running locally, are you also connecting to a local database? And then when you run it on the remote server, is the database local to the webserver? And are you updating your config file or database connection values to use the proper credentials in each environment?

You could also check to see if either the connection string is wrong once you push the code to the remote server, or the database doesn't have the proper permissions to allow connections from the remote server.
0
 
LVL 5

Expert Comment

by:innotionent
ID: 33724703
Is that all your code?
where is your mysql conection command?


$link = @mysql_connect("localhost", "username", "password") or die("<b>Could not connect to DB</b>");
	
$myquerystring = "select * from table where foo = bar";
	
if(!($result = mysql_db_query("database",$myquerystring,$link))){
	print("MySQL reports: " . mysql_error() . "\n");
		exit();
}

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33724945
Hi,
This is all I have on this page (in the way of mysql):

<?php require_once('Connections/db.php'); ?>
<?php
 include "signInChk.php";
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$season_id = date('Y');

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}


$maxRows_news = 6;
$pageNum_news = 0;
if (isset($_GET['pageNum_news'])) {
  $pageNum_news = $_GET['pageNum_news'];
}
$startRow_news = $pageNum_news * $maxRows_news;

mysql_select_db($database_db, $db);
$query_news = "SELECT * FROM news ORDER BY news_id DESC";
$query_limit_news = sprintf("%s LIMIT %d, %d", $query_news, $startRow_news, $maxRows_news);
$news = mysql_query($query_limit_news, $db) or die(mysql_error());
$row_news = mysql_fetch_assoc($news);

if (isset($_GET['totalRows_news'])) {
  $totalRows_news = $_GET['totalRows_news'];
} else {
  $all_news = mysql_query($query_news);
  $totalRows_news = mysql_num_rows($all_news);
}
$totalPages_news = ceil($totalRows_news/$maxRows_news)-1;

mysql_select_db($database_db, $db);
$query_enable_login = "SELECT disable_login FROM season";
$enable_login = mysql_query($query_enable_login, $db) or die(mysql_error());
$row_enable_login = mysql_fetch_assoc($enable_login);
$totalRows_enable_login = mysql_num_rows($enable_login);

// Initialize the Alternate Color counter
$ac_sw1 = 0;
?>

Open in new window

0
 
LVL 5

Accepted Solution

by:
innotionent earned 500 total points
ID: 33725034
i see you have the database variables in the code. but where are the actual values in your script?

that no database selected error you are getting means exactly that.

you haven't specified a database for it to query.

$database_db and $db must have values in them somewhere. just make sure that you do.
0
 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33725499
Somewhere in Connections/db.php there should be a line that calls mysql_connect(). If not, then that is part of your problem. If it's there, then you need to make sure you are not getting a connection error remove any "@" sign before the call and refresh the page, then check for any errors displayed on the page or in your PHP error log. If the connection is failing any error message should give you an indication of why. If you feel that you need to post your Connections/db.php file be sure to redact any sensitive information (e.g. username, password, database URL) first.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question